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

Member

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.

• Member
• Coach

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