topic Re: Date Range Selection Compare Previous Year in QlikView

Date Range Selection Compare Previous Year

Anonymous — Wed, 17 May 2017 18:44:16 GMT

I hope there is an easy solution for this. But i'm having trouble creating a variable that computes the previous years selection.

Selection:

SHIFT_DATE >=01/30/2017 <=05/28/2017

I want a variable for the following:

vMaxCompareDate = 05/28/2016

vMinCompareDate = 01/30/2016

My goal is to create a trend per day of previous years selection without going back and forth between selections.

Re: Date Range Selection Compare Previous Year

Anil_Babu_Samineni — Wed, 17 May 2017 18:49:35 GMT

You can create two variables for that

and then use simply

Sum({<DateField = {">=$(vMinCompareDate) <=$(vMaxCompareDate)"}>}Sales)

Sum({<DateField = {">=$(=Date(vMinCompareDate)) <=$(=Date(vMaxCompareDate))"}>}Sales)

Re: Date Range Selection Compare Previous Year

Anonymous — Wed, 17 May 2017 19:21:07 GMT

That's the Problem. I don't know how to create a variable to give me those values. Could you give some assistance for that?

Re: Date Range Selection Compare Previous Year

Anonymous — Wed, 17 May 2017 19:26:50 GMT

Sum({<DateField = {">=$(=AddYears(Date(vMinCompareDate)),-1) <=$(=AddYears(Date(vMaxCompareDate)),-1)"}>}Sales)

Re: Date Range Selection Compare Previous Year

neelamsaroha157 — Wed, 17 May 2017 19:32:24 GMT

If you are always looking at these two dates then you can hardcode in the variable OR You can use min and max or addYears or addMonths in your formula to get the desired values.

Re: Date Range Selection Compare Previous Year

Anonymous — Wed, 17 May 2017 19:43:13 GMT

It is very important, that your default date format is:

SET DateFormat='MM/DD/YYYY';

Re: Date Range Selection Compare Previous Year

Anonymous — Wed, 17 May 2017 22:54:09 GMT

Thank you for your help so far. My expression is returning a 0 value.

Here are my variables:

vMaxCompareDate: (AddYears(date(Max(SHIFT_DATE)),-1))

vMinCompareDate: (AddYears(date(Min(SHIFT_DATE)),-1))

vLaborCompareCost: Sum({<SHIFT_DATE = {'>=$(vMinCompareDate)<=$(vMaxCompareDate)'}>}Labor_Cost)

Is something wrong with my calculation?

$(vMaxCompareDate) = 05/17/2016

$(vMinCompareDate) = 01/30/2016

$(vLaborCompareCost) = 0

Re: Date Range Selection Compare Previous Year

Anil_Babu_Samineni — Thu, 18 May 2017 01:34:23 GMT

Can you check this formula @KPI?

=Sum({<SHIFT_DATE = {'>= $(=Min(SHIFT_DATE)) <=$(=Max(SHIFT_DATE))'}>}Labor_Cost)

Re: Date Range Selection Compare Previous Year

ananyaghosh — Thu, 18 May 2017 06:54:58 GMT

Hi,

Use the below documentation link for complete set of solutions and you don't have to use complex set analysis for this:

Calculation of MTD, YTD, QTD, WTD in script level - The As-Of table concept - MTD, YTD, QTD, WTD calculation made easy

Here you can find all the necessary calculations and just download the QVW file and see my solutions. I think it will fit all your requirements. If you face any problem please let me know.

Thanks

Sandip

Re: Date Range Selection Compare Previous Year

Kushal_Chawda — Thu, 18 May 2017 07:13:49 GMT

Just include '=' in vmin and vmax variable and You are done

vMaxCompareDate: =(AddYears(date(Max(SHIFT_DATE)),-1))

vMinCompareDate: =(AddYears(date(Min(SHIFT_DATE)),-1))

Re: Date Range Selection Compare Previous Year

Anonymous — Fri, 19 May 2017 17:54:36 GMT

This worked perfect.

Also my formula was off by a space between the = $ I needed to delete it.

Sum({<SHIFT_DATE = {'>= $(=vMinCompareDate)<=$(=vMaxCompareDate)'}>}Labor_Cost)

Sum({<SHIFT_DATE = {'>=$(=vMinCompareDate)<=$(=vMaxCompareDate)'}>}Labor_Cost)