Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Talk to Experts, a LIVE Q&A Webinar. Bring your Qlik Sense Business questions on Aug. 4th. Register
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Last 12 months

I need help getting last 12  months. Below is the set expression I used which worked fine until December. Now I get just last month. I would appreciate suggestions on how to fix this. Thanks

AVG({< Last12Months = {1}, DateType={'New'}>}[Time To Assign Hours])

 

Last 12 months.png

 
4 Replies
Highlighted
Specialist III
Specialist III

I'd suggest looking into the generation of your Last12Months field. How is it generated?

 

Highlighted
Creator II
Creator II

My Last12Months field is generated as below

 

If("Canonical Date" < MonthStart(Floor(Today()))
And "Canonical Date" >= SetDateYear(MonthStart(MonthStart(Floor(Today()))-1),Year(Today())-1), 1, 0) As Last12Months

Highlighted
Specialist II
Specialist II

Your formula is for YTD not last 12 months.

We use:

Round((num(monthstart(Today(1))-num(monthstart(Date(TempDate)))))/30) AS MonthsFromCurrent

to create a flag of each month, then for last 12 you would set a filter for MonthsFromCurrent = {">1<=12"}

Highlighted
Creator II
Creator II

Thanks for your suggestions but this is what worked for me 


If(("Canonical Date"<= Today() and "Canonical Date" >= MonthStart(Today(),-12)),1,0) AS Last12Months

 

Then I used the set expression below

Avg({<Last12Months = {1}, DateType = {'New'}>}[Time To Assign Hours])