How to get last 12 Months data for each prodct in Barchart

[Lakshmanudui]

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

[sunny_talwar]

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)

[Lakshmanudui]

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

[sunny_talwar · Accepted solution]

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.

[Lakshmanudui]

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

[Lakshmanudui]

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