topic Re: Previous Date Variable in Set Analysis in App Development

Previous Date Variable in Set Analysis

asmithids — Sun, 04 Jun 2017 13:57:26 GMT

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:

All Fuel Types Current Year: Sum({<Year={$(=Max(Year))}>}FuelMtrDtl_Posted_Units)
All Fuel Types Prior Year: Aggr(Sum({$<FormattedWeekEndDate={'=$(vPYWeekending)'}>}FuelMtrDtl_Posted_Units),FormattedWeekEndDate)

Re: Previous Date Variable in Set Analysis

ariel_klien — Sun, 04 Jun 2017 14:07:35 GMT

Try to use this:

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

Ariel

Re: Previous Date Variable in Set Analysis

asmithids — Sun, 04 Jun 2017 14:25:54 GMT

Hi Ariel,

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

Re: Previous Date Variable in Set Analysis

ariel_klien — Sun, 04 Jun 2017 14:31:15 GMT

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

try

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

Re: Previous Date Variable in Set Analysis

asmithids — Sun, 04 Jun 2017 14:37:42 GMT

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

Re: Previous Date Variable in Set Analysis

ariel_klien — Sun, 04 Jun 2017 14:38:54 GMT

can you attach data sample?

Re: Previous Date Variable in Set Analysis

asmithids — Sun, 04 Jun 2017 16:11:35 GMT

Hi Ariel,

Sorry for the delay. See attached sample app.

Thank you!

Re: Previous Date Variable in Set Analysis

ariel_klien — Mon, 05 Jun 2017 07:26:46 GMT

Hi,

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

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

Re: Previous Date Variable in Set Analysis

asmithids — Mon, 05 Jun 2017 11:25:23 GMT

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.

Re: Previous Date Variable in Set Analysis

ariel_klien — Mon, 05 Jun 2017 13:51:31 GMT

Try

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

Re: Previous Date Variable in Set Analysis

asmithids — Mon, 05 Jun 2017 20:52:21 GMT

Changing the quotes did not work. I tried that previously. This is strange....