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

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