Formula Syntax

[qlikviewaf]

Hi,

i'm using this expression, that is defining the Year_Surgery as follow:

I would like to modify this in order to retrieve the Year_Surgery as the selection done on the field Year, minus one year, in order to get the previous year. so if i select 2017, the year surgery needs to be 2016.

Anyone knows how to adjust the syntax?

Thanks

Year_Surgery={$(=concat(chr(39) & Year & chr(39),',') )

[tresesco]

May be this:

Year_Surgery={$(=concat(chr(39) & Max(Year)-1 & chr(39),',') )

Note: Using max() is dependent on your expected output when nothing is selected.  Use max() if you want output year as max year-1 when multiple years are in scope. Else, you can simply use Year-1

[qlikviewaf]

is not working.. i got 0 as value. any clue ?

[tresesco]

Is this variable being used in set analysis? What if when multiple values are selected or no year is selected? Could you share your sample qvw?

[qlikviewaf]

yes this is used as set analysis! exactly!

[Anil_Babu_Samineni]

Why are you using Concat() here. What if you used?

Max(Year)-1

[tresesco]

If you could answer all the other questions, it would have been easier to narrow down the issue and suggest accordingly. Meanwhile try like:

Year_Surgery={$(=concat(chr(39) & If( GetSelctedCount(Year)=1, Year-1, Year) & chr(39),',') )

[qlikviewaf]

not working ...

The request is due to the previous one that i did already working Set Analysis with selected values

Here the app attached

[qlikviewaf]

The request is due to the previous one that i did already working Set Analysis with selected values

[tresesco · Accepted solution]

May be this?

Variable =concat(chr(39) & $(=Year-1) & chr(39),',')

Exp=Sum({<Month_Surgery={$(=concat(Distinct chr(39) & Month & chr(39),',') )},Year_Surgery={$(vLastYear)},Day=,Year=,Month=>}Amount)