topic Re: Previous 12 Months in QlikView

Previous 12 Months

Thu, 07 May 2015 10:39:07 GMT

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

Re: Previous 12 Months

Gysbert_Wassenaar — Thu, 07 May 2015 10:45:30 GMT

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)

Re: Previous 12 Months

Thu, 07 May 2015 10:49:14 GMT

PFA

Re: Previous 12 Months

Thu, 07 May 2015 10:55:21 GMT

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)

Re: Previous 12 Months

Gysbert_Wassenaar — Thu, 07 May 2015 11:00:26 GMT

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)

Re: Previous 12 Months

Thu, 07 May 2015 11:05:05 GMT

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?

Re: Previous 12 Months

Gysbert_Wassenaar — Thu, 07 May 2015 11:09:54 GMT

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

Re: Previous 12 Months

Thu, 07 May 2015 11:12:43 GMT

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

Re: Previous 12 Months

Gysbert_Wassenaar — Thu, 07 May 2015 11:16:57 GMT

Can you post a qlikview document that demonstrates the problem?

Re: Previous 12 Months

Thu, 07 May 2015 11:19:38 GMT

PFA

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

Re: Previous 12 Months

Thu, 07 May 2015 13:23:52 GMT

I forgot to mention, there is no data for December 14 as an example, but I want it to show up as 0 on the graph (same as May 15)

Re: Previous 12 Months

Gysbert_Wassenaar — Thu, 07 May 2015 14:10:36 GMT

See attached qvw.

Re: Previous 12 Months

Thu, 07 May 2015 14:19:41 GMT

Thank you Gysbert,

I can see what you have done but is there a way to show 0 on the graph?

Re: Previous 12 Months

Gysbert_Wassenaar — Thu, 07 May 2015 14:20:51 GMT

Enable the Zero on Bars option on the Presentation tab.

Re: Previous 12 Months

Thu, 07 May 2015 14:23:36 GMT

Fantastic!!

Thank you so very much for your time and patience helping me with this