Date Range Selection Compare Previous Year

[Anonymous]

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.

[Anil_Babu_Samineni]

You can create two variables for that

and then use simply

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

Or

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

[Anonymous]

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?

[Anonymous]

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

[neelamsaroha157]

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.

[Anonymous]

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

SET DateFormat='MM/DD/YYYY';

[Anonymous]

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

[Anil_Babu_Samineni]

Can you check this formula @KPI?

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

[ananyaghosh]

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 cal...

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

[Kushal_Chawda · Accepted solution]

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))