Solved: Using IF with a variable in expression - Qlik Community

ashokunbi · ‎2018-09-14

I am trying to use an if statement with a variable in my expression and I get no results. The variable works when I use the variable on it's own but when used with the if I get no results.

I have tried:

if(OrderQtr='Apr-Jun 2018',$(vAvgOrderCost),0)

if(OrderQtr='Apr-Jun 2018',sum($(vAvgOrderCost)),0)

sum($(vAvgOrderCost)if(OrderQtr='Apr-Jun 2018',0))

Nothing seems to work. Thanks

rubenmarin · ‎2018-09-18

I see whats happening, you don't have a dimensión, so the if() always returns null() because each row has all quarternumbers, usually to add filters other than dimensions you need to use set analysis, wich will broke the use you are doing of variables, or you will need to pass the quarter as parameter

One quick fix can be adding an aggr to the expression, so each row will virtually check the quarters one by one:

Sum(Aggr(if(NumOrderQtr=43191,$(vAvgOrderCost),0), NumOrderQtr))

View solution in original post

YoussefBelloum · ‎2018-09-14

Hi,

what is the content of the variable ?

rubenmarin · ‎2018-09-14

Hi, and also.."if(OrderQtr='Apr-Jun 2018',1,0)" returns '1' in the expected quarter?

ashokunbi · ‎2018-09-14

The variable content is =Sum([Unit Cost PO])/Count([Unit Cost PO])

Also if(OrderQtr='Apr-Jun 2018',1,0)" did not return 1 in the expected quarter

Thanks

ashokunbi · ‎2018-09-14

The variable content is =Sum([Unit Cost PO])/Count([Unit Cost PO])

Thanks

dplr-rn · ‎2018-09-14

what is the column type of OrderQtr. that maybe the issue there

ashokunbi · ‎2018-09-14

Money

Thanks

dplr-rn · ‎2018-09-14

not sure why OrderQuarter is money but try converting it to string before doing the compare

YoussefBelloum · ‎2018-09-14

try to remove the = sign on the variable and see if the expressions works

ashokunbi · ‎2018-09-14

I'm sorry OrderQtr is not money. It is text. The result of the expression is money.