Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have data like Products, MonthYear and Sales data.
I have a bar chart in that Products having dimension and I want to show the last 12 months Sales data for each Product in Bar chart.
I am using below code
=Aggr(Sum( {< MonthYear = {"$(= '>'Addmonths(
Max(Date((Date#([MonthYear], 'MMM-YY')),'MMM-YY'),-11),'MMM-YY') &' <= '&
Date(Max(Date#([MonthYear], 'MMM-YY')),'MMM-YY')"
} > } Sales ),Products)
My Data like
Products, MonthYear, Sales
Cloths , Jan-19 , 34
Cloths , Feb-19 , 56
Cloths , Mar-19 , 7
Cloths , Apr-19 , 56
Cloths , May-19 , 21
Cloths , Jun-19 , 34
Can any one please help me.
Thanks & Regards,
Lakshman
If that is the case, I would use another field (like may be date). The reason I say this is because >=, >, <, <= only work with fields which have numeric underlying value. If MonthYear is not dual, then your set analysis won't work unless you create a string of all possible MonthYear you want to include.
How is MonthYear field created in the script? Do you use something like this?
Date(MonthStart(DateField), 'MMM-YY') as MonthYear
If you do, then try this
Sum({<MonthYear = {"$(='>' & Date(MonthStart(Max([MonthYear]), -11), 'MMM-YY') & '<=' & Date(Max([MonthYear]), 'MMM-YY'))"}>} Sales)
Hi Sunny,
Thanks for your quick replay.
I didn't create any derived field in scripting side, MonthYear field is directly comming from source and formate is 'MMM-YY'.
Can you please tell me how to mention in setanalysis.
Thansk & Regards,
Lakshman
If that is the case, I would use another field (like may be date). The reason I say this is because >=, >, <, <= only work with fields which have numeric underlying value. If MonthYear is not dual, then your set analysis won't work unless you create a string of all possible MonthYear you want to include.
Hi Sunny,
Thanks for your replay.
Can you please explain how to create dual month field in Script and how to use set analysis.
I am getting confessed.
Thanks & Regards,
Lakshman
Hi Sunny,
Thanks for your help.
Finally I got the solutions.
I have created Date field in backend.
Load
Products,
Month-Year,
Sales,
Date(Date#(Month-Year ,'MMM-YY')) AS Date
From Products;
In Set analysis
=Sum({<Month-Year=, Date={">=$(=MonthStart(Max(Date), -11))<=$(=Max(Date))"}>}Sales)
Thanks & Regards,
Lakshman