topic Re: Formula Syntax in QlikView

Formula Syntax

qlikviewaf — Tue, 12 Dec 2017 10:07:41 GMT

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),',') )

Re: Formula Syntax

tresB — Tue, 12 Dec 2017 10:12:00 GMT

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

Re: Formula Syntax

qlikviewaf — Tue, 12 Dec 2017 10:24:50 GMT

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

Re: Formula Syntax

tresB — Tue, 12 Dec 2017 10:29:21 GMT

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?

Re: Formula Syntax

qlikviewaf — Tue, 12 Dec 2017 10:30:23 GMT

yes this is used as set analysis! exactly!

Re: Formula Syntax

Anil_Babu_Samineni — Tue, 12 Dec 2017 10:30:43 GMT

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

Max(Year)-1

Re: Formula Syntax

tresB — Tue, 12 Dec 2017 10:35:24 GMT

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),',') )

Re: Formula Syntax

qlikviewaf — Tue, 12 Dec 2017 10:43:15 GMT

not working ...

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

Here the app attached

Re: Formula Syntax

qlikviewaf — Tue, 12 Dec 2017 10:49:08 GMT

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

Re: Formula Syntax

tresB — Tue, 12 Dec 2017 10:58:52 GMT

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)

Re: Formula Syntax

qlikviewaf — Tue, 12 Dec 2017 11:10:17 GMT

amount of 01/01/2016 should be 1100 , so basically the sum of the 3 lines abow.

IN your example is not 1100

Re: Formula Syntax

tresB — Tue, 12 Dec 2017 11:45:30 GMT

In your data for NickRep there are 3 amounts (all 100). See the frequency of the values in the screenshot. Hence you get the sum as 1300 instead of 1100.

Re: Formula Syntax

qlikviewaf — Tue, 12 Dec 2017 11:49:42 GMT

that formula is build to select Year= 2017 and Month = Janauary, but get on the graph all the sales where Year_Surgery= 2016 and Month_Surgery= January regardless what is selected in terms of month/year.

So i expected for Year=2017 and Month=January, the sum of all surgery falling in 2016 , january.

Make sense?

Re: Formula Syntax

tresB — Tue, 12 Dec 2017 11:56:56 GMT

In the screenshot above, all the data (1300) fall under surgery date 2017, Jan. I am sorry, I guess I could not follow.

Re: Formula Syntax

qlikviewaf — Tue, 12 Dec 2017 12:01:59 GMT

Sorry i was looking the table which is wrong

it works, thank you!