Qlik Community

Ask a Question

New to Qlik Sense

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

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
coloful_architect

moving annual total/ selective month_year for line chart

Hi Masters:

I now have data from Jan 19 to Oct 20, presenting as 9/1/2020.

What if I only want to show the latest 12 month/ moving annual months: Nov 19 to Oct 20 at my line chart

what set expression I should put? or any smart way?

Thanks

coloful_architect_0-1607104521881.pngcoloful_architect_1-1607104594326.png

 

6 Replies
GaryGiles
Specialist
Specialist

Include the following set analysis in your measure expression:

{$<[Calendar Year/Month]={"<=$(=max(Total [Calendar Year/Month]))>$(=AddMonths('$(=max(Total [Calendar Year/Month]))',-12))"}>}

for example, if you are summing Amount, it would be:

sum({$<[Calendar Year/Month]={"<=$(=max(Total [Calendar Year/Month]))>$(=AddMonths('$(=max(Total [Calendar Year/Month]))',-12))"}>} Amount)

coloful_architect
Author

 

 

the formula check goes "ok" ...but the line chart still stay with all the months rather than 12 months.

I attach here for you to see if the coding is correct.

coloful_architect_0-1608584961730.png

 

sunsun566
Contributor III
Contributor III

Try this?

Sum({<Year= ,Month=, [Calendar Year/Month] = {">=$(=AddMonths(Max([Calendar Year/Month])), -12)<=$(=Max([Calendar Year/Month])) "}>} Amount)

coloful_architect
Author

hi guys, thanks for your answers. I think I sort of getting the knack from your answers.

I try to play around by following your logic. Now I am stuck with that I can filter into time frame that I want.

like from 20 months into 12 months. but at that 12 months, I CAN NOT select one or multiple months particularly.

but the filter panel does show the selective month. The bar chart just do not change accordingly.

is that because I do not follow a good syntax ?

please advise.

Ksrinivasan
Specialist
Specialist

hi,

You can try this,

(Sum({$<Month_Year= {'Nov-19',Dec-19','Jan-20','Feb-20','Mar-20','Apr-20','May-20','Jun-20','July-20','Aug-20','Sep-20','Oct-20'}>}Unitcost)

and also between month set functions are there.

 

regards,

Ksrinivasan

coloful_architect
Author

Thanks Ksrinivasan,

I am now using a variable at my set statement for an automation like this 

sum({<[Month]={'>=v_Month)'}>}[Sales]

I know if I use hard code like your way, I can still select particular month.

but any way I can achieve that by using variable ?  now it just shows me 12 month but I CAN NOT select any or multiple months...

thanks