topic getting previous year value with if statement in QlikView

getting previous year value with if statement

Tue, 24 Sep 2013 11:57:46 GMT

Hi all,

I'm migrating some reports from BusinessObjects to QlikView. I find difficult to calculate the previous year price for materials. I tried in two ways:
- using set analysis: =Sum({<YEAR = {$(vPY)}>} PRICE)
- using if statement: =sum(if(YEAR = vPY, PRICE)
where vPY = max(YEAR)-1

The problem is that if I use set analysis I don't get the values I expect (even there are little differences) because BusinessObjects uses If Statement in formulas and I imagine the set analysis logic is different from if statement logic.
I'd like to choose if statement way instead of set analysis but the problem is that my formula doesn't work. It displays no values in the column. Can you help me getting the previous year price value using if statement, please?
Another solution I was thinking was to calculate the value into the load script, but the PRICE is calculated based on fields from different tables and for a newbie like me it seems pretty difficult.

Any ideas?
thanks

Re: getting previous year value with if statement

Tue, 24 Sep 2013 20:13:43 GMT

Hi !

You need to use double quotes, like this:

=Sum({<YEAR = {"$(vPY)"}>} PRICE)

or for static year

=Sum({<YEAR = {"$(=year(today())-1)"}>} PRICE)

Bye !

Re: getting previous year value with if statement

Oleg_Troyansky — Tue, 24 Sep 2013 21:37:06 GMT

Double quotes are only needed for search, so I don't believe that this is the problem in this case.

I think what's missing in the Set Analysis formula is the equal sign within the $-sign expansion. The variable contains a formula, hence the $-sign expansion needs to begin with an equal sign. Or, the other way - the variable can include the equal sign and then the set analysis won't need it:

using set analysis: =Sum({<YEAR = {$(=vPY)}>} PRICE)

where vPY = 'max(YEAR)-1'

using set analysis: =Sum({<YEAR = {$(vPY)}>} PRICE)

where vPY = '=max(YEAR)-1'

The formula with an IF has similar problems, but using IF is not recommended anyway, so I won't waste your time explaining it...

You can learn more about $-sign expansions from my recent blog:

http://www.naturalsynergies.com/q-tip-6-those-tricky-sign-expansions/

cheers,

Oleg Troyansky