10 Replies Latest reply: May 19, 2017 1:54 PM by Jacob Salas

Date Range Selection Compare Previous Year

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

You can create two variables for that

and then use simply

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

Or

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

• Re: Date Range Selection Compare Previous Year

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

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

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

SET DateFormat='MM/DD/YYYY';

• Re: Date Range Selection Compare Previous Year

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

Here are my variables:

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

Can you check this formula @KPI?

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

• Re: Date Range Selection Compare Previous Year

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

• Re: Date Range Selection Compare Previous Year

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)

• Re: Date Range Selection Compare Previous Year

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