topic MonthsAgo not working as expected in QlikView

MonthsAgo not working as expected

pauledrich — Thu, 20 Feb 2014 17:01:40 GMT

I use a Master Calendar which has the line to create MonthsAgo

12*(Year(Today())-Year(TempDate))+Month(Today))-Month(TempDate) as MonthsAgo

and I use the expression

Sum({$<MonthsAgo{'>=6<=12'}>}Sales)

Both work as you would expect.

I use Nprinting and want to apply a filter using 'MonthsAgo' and have found it doesn't work.

To investigate the problem I have all of my dates (Months, Years, MonthsAgo etc) in list boxes and select say 2014 from Year - I see MonthsAgo correctly selected and my tables change to only show the associated date range. If I select a range of dates from the months ago list box the related dates change in the list boxes but my charts and tables are not affected.

Can someone help with this problem?

Many thanks

Re: MonthsAgo not working as expected

Gysbert_Wassenaar — Thu, 20 Feb 2014 19:12:24 GMT

Sum({$<MonthsAgo{'>=6<=12'}>}Sales) means apply all selections except the selection in MonthsAgo and instead use only the MonthsAgo values larger or equal to 6 and smaller or equal to 12. If you don't want to overrule the selection in MonthsAgo you need to change the expression so Sum(Sales).

Re: MonthsAgo not working as expected

Sat, 22 Feb 2014 00:15:36 GMT

Hi Paul, As Gysbert stated above, the set analysis ignore the SET Modifiers selections. If you need to apply the MonthAgo selections you need to re write your expression like below:

IF( GETSELECTEDCOUNT(MonthAgo) = 0 , Sum({$<MonthsAgo{'>=6<=12'}>}Sales) , Sum(Sales) )

In the above expression, if you make any selections on MonthsAgo Sum(Sales) will be calculated otherwise your original expression will be calculated.

Re: MonthsAgo not working as expected

pauledrich — Mon, 24 Feb 2014 13:00:02 GMT

Thank you for both replies very helpful.

I understand the syntax and I have not given the best explanation. My dimension is MonthName' MMM/YYYY' for my pivot table which for reporting purposes I only want the last 13 months including the most recent Month displayed; as an example I have 'Sum({$<MonthsAgo={'>=0<=13'}>}Sales)' .

If I use a list box for MonthName and only select say Dec2013 - the pivot changes and only shows that one month, if I however select '2' from Months ago - I see the Year selector change to '2013' and Months to 'December' with no change to the pivot table, clearly the addition of months ago in the expression causes the issue as I applied the calculation provided by dathu which works and makes sense, but also means I need to now change all expressions in all the dashboards which I really would like to avoid, so I have created the following script line which works well.

if(TempDate>=MonthStart(AddMonths($(vToday),-13)) AND TempDate<MonthStart($(vToday)-1),1,0) as ReportFlag

I can now create a Variable and apply the filter in Nprinting to only show the selected period, without the need to change 100's of expressions.

However - I can see there being a need to simply apply as a bookmark across all dashboards and to have this refresh, daily or on opening. Is this easily achieved?

Thanks again.