5 Replies Latest reply: Aug 12, 2016 4:24 PM by Pablo del Cerro

# Dynamic Filter values

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

• ###### Re: Dynamic Filter values

Hi Govind,

try this,

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

Thanks

Kumar

• ###### Re: Dynamic Filter values

May be try this:

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

• ###### Re: Dynamic Filter values

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.

• ###### Re: Dynamic Filter values

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?

• ###### Re: Dynamic Filter values

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