Using Year column in If condition

[privatevinodh]

Hi Am new to qlik sense i have a doubt as to how to use a Year column in If function.

I have a calendar table as part of my data model with cols like Year, Month, Day etc

i have another table stocks with cols like Stock on hand, Stock on order etc

Both these tables are joined by a col call 'YearMonth' (Sample values like 20211,20212,20213 etc)

Now my query am trying to create a KPI object with the below expression

sum(if(year=2015), Stock on hands) - I want to return the sum of stocks for the whole of 2015 alone

but am getting a wrong summation all the time am i missing something here ? Kindly help !

[JuanGerardo]

Hi @privatevinodh, try to avoid IF() when possible, it has a poor performance. You should use set analysis instead, something like: Sum({<Year={2015}>} [Stock on hands])

JG

[privatevinodh]

Hi Juan, Thanks for the reply. The idea is i wanted to compare Set analysis and Alternate state

So i wanted to create a normal KPI chart with IF and apply Alternate state to it, then

Compare the same with another KPI chart with Set analysis. Hence this query.

While using IF am i missing something ?

[Kushal_Chawda]

@privatevinodh  what does below gave you?

sum(if(year=2015, Stock on hands) )

[privatevinodh]

Wrong Summation, Its throwing up a number which is not the correct summation

[Kushal_Chawda]

@privatevinodh  try below

sum(aggr(if(year=2015, Stock on hands) ,year))

[JuanGerardo]

Hi, using alternate states is full compatible with using set analysis. I don't see the need for an IF() expression here.

Anyway, your expression is not syntactically correct, it should be: sum(if(year=2015, [Stock on hands]))

JG