How to Make a Card of Total Ratio and Recent 1 Year Ratio

Hi There,

I need DOMO Dojo expertise to help me on create 2 card using Beastmode calculation.

I have a basic data to create this 2 card:

  1. Month date (201706, 201707, 20178 etc)
  2. Product name, 2 Type: Panel & MB
  3. Monthly defect qty
  4. Cumulative sales

Idea:

The Beastmode I should create is:

  1. Cumulative Monthly Defect Qty
  2. Total Ratio
  3. Recent 1 Yr Cumulative sales
  4. Recent 1 Yr Monthly defect qty
  5. Recent 1 Yr Cumulative Monthly Defect Qty
  6. Recent 1 Yr Ratio

Card Created:

  1. I need to create 2 card which is only 1 product
  2. Combination of 2 product
  3. I could make this kind of trend in excel, but I'm not able to make in DOMO, due to my limitation of Beastmode calculation since I'm not a programmer student.
  4. Besides, I have attached the excel file for better understanding on what kind of card I would like to get.


Answers

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    @LMS ,

    in the future, please be specific about what you're unable to accomplish (other than 'everything') it seems like you're asking us to provide consulting services -- which i don't think is a reasonable ask from the community site in my opinion.


    you can calculate cumulative sales in your ETL or you can do it in Analyzer and avoid pre-aggregation.

    to calculate cumulative sum you'll need to use window functions. you'll need your CSM to enable the feature.

    you may want to partition by Product depending on your requirements

    sum(sum(amount)) over ( partition by product order by date asc) 
    

    if you've never seen window functions before, here's a tutorial video. it doesn't specifically cover cumulative sum, but window functions are a set of advanced functions that you'll come across frequently.


    to calculate permutations of this_Value_one_year_ago or rolling_12_months, instead of building complicated beast modes, I would add a column in ETL that counts the number of months, currentMonth_lag from the current_date() , so Feb 21 would be 0, Jan would be 1, Dec 2020 would be 2 , March 21 would be -1 April 21 would be -2.


    That way you can use

    case when currentMonth_Lag = 12 then ... end
    

    etc.


    Please be specific what your problem is if you have follow up questions. good luck.

  • LMS
    LMS ⚪️

    Thank you for the suggested card building, although this is not the card i wanted, but i will try to figure out myself.

Sign In or Register to comment.

Hey, Stranger!

It looks like you're new here. Those who sign in get access to engage with even MORE fire content. To get involved, click one of these buttons!