Set analysis with dynamic dates

[Anonymous]

Hi All,

I know the question f set analysis with dynamic dates has been asked a fair bit, but I cant seem to get my analysis to work. I have a date field called PERIOD which has the format 01.04.2018 (so first of the month and year). This is the same for all of the data in this field. I am then trying to create a table where I can toggle between years and the sum changes. My expression is:

Sum({$<PERIOD={"$(=Date(01.04.&(Year-1), 'MM/DD/YYYY'))"}>} TOTAL)

Where Year is the field I want to update, so if you select 2018 it gives you the 2018 dates and if its 2019 then its all the 2019 dates etc etc.

I cant for the life of me figure out what Im doing wrong. Can anyone help?

Thanks!!

[sunny_talwar]

If you select 2018, it should give all dates from 2018? Why do you need set analysis to do that... QlikView or Qlik sense by default will show you all possible dates from 2018 if you select 2018? Are you may be selecting on Month or date and you don't want them to filter down your expression?

[Anonymous]

Hi Sunny,

Sorry - I wasnt very clear. Im creating a table and need the calculation in the table to be update when I select a year filter. I have several years to choose from and just want to update it based on the year chosen:

Account     Apr     May     Jun

XXX          xx          xx        xx

So, if I have filtered on Year 2018, I'd like it update the month data to be data for April 2018, May 2018 etc.

Does that make better sense?

[sasiparupudi1]

May be try

Sum({$<PERIOD={"$(=Date(Max(PERIOD), 'MM/DD/YYYY'))"}>} TOTAL)

[Anonymous]

You can define year in load script as well as correctly format the date.

[Anonymous]

Hi Sasidhar - that wont work because I need to distinguish between months in the table:

Account    Apr    May    Jun

XXX          xx          xx        xx

which is why I have the formula above becasue I could specify the day and month, but let the year be dynamic (thats the only part that really changes:

Sum({$<PERIOD={"$(=Date(01.04.&(Year-1), 'MM/DD/YYYY'))"}>} TOTAL)

Fixed for every column in the table

Dynamic (based on filter selection)

[Anonymous]

Could you please post a compressed version of the app?

[Anonymous]

I already have year in the load script. What I want to understand is how I pass that filter value to a measure when it is changed.

[njmaehler]

Hi Jas,

Dates always cause issues. Just watch out for the date being DD.MM&(Year) or MM.DD&(Year) compared to 'MM/DD/YYYY'.

I find it is easiest to put the value you want within the Set Analysis into a variable and rather use that.

vMaxYear = max(Year);

or

vMaxMonthYear = MonthStart(max(Date));

The set analysis will be something like:

Sum({$<PERIOD={"$(=Date(01.04.&$(vMaxYear), 'MM/DD/YYYY'))"}>} TOTAL)

or

Sum({$<PERIOD={'$(vMaxMonthYear)'}>} TOTAL)

Option one can get a bit "confused". Try hard coding the value to ensure that the dates are being interpreted correctly.

i.e. build the set analysis up slowly to work out where you are going wrong.

Also check how your dates are set on your main tab - this can be conflicting.

When I have issues with dates I often use DayName(PERIOD) rather than Date(PERIOD, 'MM/DD/YYYY')

[Anonymous]

Hi Nicky,

Thanks for that - I tried it with the variable and that actually worked! So thank you for taking the time to respond.

Thanks

JAs