Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
asmithids
Partner - Creator II
Partner - Creator II

Previous Date Variable in Set Analysis

Hello,

I'm trying to show current and last year's weekend date sales (see attached).  I am using a variable below to calculate last year's weekend date.  The text box shows that the variable is working because I have a filter on the weekend field set to 5/27/2017 and the variable is showing the comparable date last year.  The issue is that the "All Fuel Types Prior Year" column in the straight table is showing the filter date (i.e. 5/27/2017) value and not the value from 5/28/2016. 

How anyone seen this issue before?  Thank you in advance for any assistance!

Variable:

vPYWeekending: =Max(FormattedWeekEndDate)-364

Expressions:

  1. All Fuel Types Current Year:  Sum({<Year={$(=Max(Year))}>}FuelMtrDtl_Posted_Units)
  2. All Fuel Types Prior Year:  Aggr(Sum({$<FormattedWeekEndDate={'=$(vPYWeekending)'}>}FuelMtrDtl_Posted_Units),FormattedWeekEndDate)
10 Replies
ariel_klien
Specialist
Specialist

Try to use this:

vPYWeekending: =addmonths(Max(FormattedWeekEndDate),-12)


BR


Ariel

asmithids
Partner - Creator II
Partner - Creator II
Author

Hi Ariel,

Thank you for the reply.  That did not work.  It produced a previous date of 12/30/1899.  See attached.

ariel_klien
Specialist
Specialist

I think the format of the field is not recognize...

try

vPYWeekending =addmonths((Max(Date#(FormattedWeekEndDate,'MM/DD/YYYY')),-12)

asmithids
Partner - Creator II
Partner - Creator II
Author

Same result (i.e. 12/30/1899). 

ariel_klien
Specialist
Specialist

can you attach data sample?

asmithids
Partner - Creator II
Partner - Creator II
Author

Hi Ariel,

Sorry for the delay.  See attached sample app.

Thank you!

ariel_klien
Specialist
Specialist

Hi,

you need to erase the equal sine in the variable....

and you don't need the Date#....

1280919.png

asmithids
Partner - Creator II
Partner - Creator II
Author

Hi Ariel,

Made the change but, the Prior Year column is still showing the Current Year.  See attached.

I get the right numbers when I hardcode the prior year date in the PY expression like this:

Sum({$<FormattedWeekEndDate={'5/28/2016'}>}FuelMtrDtl_Posted_Units)

Even using the following expression without Aggr still shows the current year numbers:

Sum({$<FormattedWeekEndDate={'=$(vPYWeekending)'}>}FuelMtrDtl_Posted_Units)

None of the expressions above are expanding the variable correctly.  The KPI in the screen shot does correctly expand the variable.   


I believe the straight table is in the same "grain" with respect the the dimensions being used. 

ariel_klien
Specialist
Specialist

Try

Sum({$<FormattedWeekEndDate={"$(vPYWeekending)"}>}FuelMtrDtl_Posted_Units)