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.