Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

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

5 Replies
kumarkp412
Contributor II

Re: Dynamic Filter values

Hi Govind,

try this,

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


Thanks

Kumar

MVP
MVP

Re: Dynamic Filter values

May be try this:

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

Not applicable

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.

MVP
MVP

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?

pablodelcerro
New Contributor

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