Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
Lakshmanudui
Contributor II
Contributor II

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

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

1 Solution

Accepted Solutions
sunny_talwar

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.

View solution in original post

5 Replies
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
Contributor II
Contributor II
Author

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

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.

View solution in original post

Lakshmanudui
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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