Dynamic Filter values

[#1]

Hi

I am using Qlik sense.

If i pass variable values then i can able to see correct values.

I defined VpreviouseYear=2015 in load manager.

Sum({$<[ACT_END_DATE.autoCalendar.Year] = {$(VPreviouseYear)}>} [Grand Total])

But this is hardcoded.

My requirement is When i select my filter pane (ACT_END_DATE.autoCalendar.Year) 2015 then my Grand Total should be 2014 sum of grand total..

How can i achieve this.

Thanks

Govind R

[kumarkp412]

Hi Govind,

try this,

Sum({$1<[ACT_END_DATE.autoCalendar.Year] = {$(VPreviouseYear)}>} [Grand Total])

Thanks

Kumar

[sunny_talwar]

May be try this:

Sum({$<[ACT_END_DATE.autoCalendar.Year] = {$(=Max([ACT_END_DATE.autoCalendar.Year]) - 1)}>} [Grand Total])

[#4]

No luck.

Can you please help.If i choose 2015 in my prompt then my grand total should be 2014

If i choose 2014 then my grand total should be 2013......

How can i get it.

[sunny_talwar]

Above expression should have worked the way you described, would you be able to provide a sample to show it did not work this way?

[pablodelcerro]

Hi there,

Sunny's solution perhaps is not working because the Autogenerate Year field is a dual with Text and Date. If you see the definition it states:

Dual(Year($1), YearStart($1)) AS [Year]

So, if you MAX and -1, it will bring you last day of previous year.

You need to work with formulas like Year() if you want to have the set analysis in TEXT, o YearStart() if you want to use the set analysis with date (the num part of the dual).

Not sure if Qlik will "fix" this dual field, so be carefull for what changes may happen in the future.

So, i guess something like this should work on your variable:

=Year(Max([FECHA.autoCalendar.Year]))-1

or

=YearStart(Max([FECHA.autoCalendar.Year])-1)

The first example on the SetAnalysis you will need to say '$(VpreviouseYear)' because it will be text. On the second one it will be a date.

I hope this helps.

Pablo