Previous 12 Months

[#1]

Apologies if I have duplicated other threads but I have had a look through and could not find the answer or better still understand the concept

In my document I have a graph on the details tab which has been setup to look at the previous 12 months from the previous month back.

I want to now convert this to current month (so it updates when data is refreshed).

How do I amend the expression below so that it updates and also 0 values are shown. I have a master calendar setup

({$<DateRaised={">=$(=addmonths(monthstart(today()),-13)) 00:00:00 <=$(=addmonths(Monthend)today()),-2,1) 23:59:59"},ValueID={'*24*'}, IncidentType={'*Accident*'}, ListTitle={'*Employee*'}>}ValueID)

Thanks again in advance

[Gysbert_Wassenaar]

MonthEnd returns a timestamp so you don't have to add 23:59:59. And MonthStart and MonthEnd also accept an offset parameter so AddMonths isn't needed:

count({$<DateRaised={">=$(=MonthStart(Today(),-13))<=$(=MonthEnd(Today(),-1))"},ValueID={'*24*'}, IncidentType={'*Accident*'}, ListTitle={'*Employee*'}>}ValueID)

[#3]

PFA

[#4]

Thanks Both, Venkat I need it to be automated on current month.

Gybert, your function does not allow the current month/active month to show, how can I amend it to say from now back 12 months (May 15 to June 14)

[Gysbert_Wassenaar]

If you don't want to show the previous 12 months, but the previous 12 months and the current month then change MonthEnd(Today(),-1) in MonthEnd(Today()).

If you want to show the last 365 days use:

count({$<DateRaised={">=$(=AddYears(Today(),-1))<=$(=Today())"},ValueID={'*24*'}, IncidentType={'*Accident*'}, ListTitle={'*Employee*'}>}ValueID)

[#6]

Thanks Gysbert, that is ideal but months with 0 are not appearing, despite having a Master Calendar set up?

Do you know why this might be?

[Gysbert_Wassenaar]

Disable the Suppress Zero values option on the Presentation tab of the charts properties window.

[#8]

Thanks Gysbert, I tried that already but I fear the issue is my dates

[Gysbert_Wassenaar]

Can you post a qlikview document that demonstrates the problem?

[#10]

PFA

The graph on the details tab is the one I am wrestling with (so to speak)