topic Re: AddMonths function to vStartDate - selecting arbitrary date ranges in QlikView

AddMonths function to vStartDate - selecting arbitrary date ranges

Tue, 01 Mar 2016 17:19:44 GMT

Hi All

I've been asked to create an app for revenue reporting that allows the end user to input the data ranges the user wants to view - I watched the "Selecting Arbitrary date ranges" video on YouTube, and added the functionality to the app. I was extremely happy with this, and so was the chap who requested the app in the first place - but now he's asked for a prior period comparative which shows the previous year to the arbitrary date ranges selected.

The formula to show the revenue for the period selected by the user I have created is this:

=Sum({$<Month_YYYY={">=vStartDate<=vEndDate}>}Revenue)

Having had a think about it, I think AddMonths is the right way to go with this, so I came up with this formula:

=Sum({$<Month_YYYY={">=(date(addmonths(Date#('$(vStartDate)','MMM-YYYY'),-12),'MMM-YYYY')<=(date(addmonths(Date#('$(vEndDate)','MMM-YYYY'),-121),'MMM-YYYY')"}>}Revenue)

What I'm expecting to see is if the user selected Jan-2015 as the start date and May-2015 as the end date, the sum of Revenue for Jan-2014 to May-2014.

Depressingly though, I'm seeing the exact same value as for the first formula. I've spent the past four hours moving single quotes and double quotes around and adding and removing brackets, and I'm utterly at a loss now.

Can anyone help me figure out where I'm going wrong?

Thanks in advance

Lisa

Re: AddMonths function to vStartDate - selecting arbitrary date ranges

sunny_talwar — Tue, 01 Mar 2016 17:47:59 GMT

Can you try this:

=Sum({$<Month_YYYY={">=$(Date(AddMonths(Date#('$(vStartDate)','MMM-YYYY'), -12), 'MMM-YYYY')<=$(Date(AddMonths(Date#('$(vEndDate)', 'MMM-YYYY'), -12), 'MMM-YYYY')"}>}Revenue)

This won't work if you have Month_YYYY as your dimension. In that case you will need to use Above() or Below() functions based on your sorting.

Re: AddMonths function to vStartDate - selecting arbitrary date ranges

Wed, 02 Mar 2016 09:36:54 GMT

Thanks Sunny - tried it, but no change. I'm not using Month_YYYY as a dimension, only client names.

Re: AddMonths function to vStartDate - selecting arbitrary date ranges

Anonymous — Wed, 02 Mar 2016 09:47:29 GMT

try like this?

=Sum({$<Month_YYYY={">=$(=date(addmonths(Date#('$(vStartDate)','MMM-YYYY'),-12),'MMM-YYYY')<=$(=date(addmonths(Date#('$(vEndDate)','MMM-YYYY'),-12),'MMM-YYYY')"}>} Revenue)

Re: AddMonths function to vStartDate - selecting arbitrary date ranges

Wed, 02 Mar 2016 10:12:55 GMT

Tried that one too - no change. Thanks for replying though! Very much appreciated.

Re: AddMonths function to vStartDate - selecting arbitrary date ranges

sunny_talwar — Wed, 02 Mar 2016 15:20:56 GMT

What about if you remove single quotes from around your variables:

=Sum({$<Month_YYYY={">=$(Date(AddMonths(Date#($(vStartDate),'MMM-YYYY'), -12), 'MMM-YYYY')<=$(Date(AddMonths(Date#($(vEndDate), 'MMM-YYYY'), -12), 'MMM-YYYY')"}>}Revenue)

or removing both the single quote and dollar sign expansion

=Sum({$<Month_YYYY={">=$(Date(AddMonths(Date#(vStartDate,'MMM-YYYY'), -12), 'MMM-YYYY')<=$(Date(AddMonths(Date#(vEndDate, 'MMM-YYYY'), -12), 'MMM-YYYY')"}>}Revenue)

Re: AddMonths function to vStartDate - selecting arbitrary date ranges

Mon, 07 Mar 2016 10:12:03 GMT

Okay, so I have a solution to this - but using addmonths in the set analysis wouldn't work, no matter what variation of dollar expansion, single quotes or double quotes I tried!

In the end, I set two additional variables - based off my vStartDate and vEndDate, and applied the addmonths function to them: =addmonths('$(vStartDate)', -12)

Works perfectly. Thanks Sunny and Balraj for your help though.