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

LMS

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.

LMS

RawData Total Ratio & Recent 1 Yr Ratio v1.xlsx

jaeW_at_Onyx

@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.

https://www.youtube.com/watch?v=cnc6gMKZ9R8

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

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