Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

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.

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

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

View solution in original post

10 Replies
Highlighted

You can create two variables for that

and then use simply

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

Or

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Contributor III
Contributor III

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?

Highlighted
Anonymous
Not applicable

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

Highlighted
Specialist II
Specialist II

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.

Capture.PNG

Highlighted
Anonymous
Not applicable

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

SET DateFormat='MM/DD/YYYY';

Highlighted
Contributor III
Contributor III

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



Highlighted

Can you check this formula @KPI?

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Creator III
Creator III

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

Highlighted
MVP
MVP

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

View solution in original post