Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

ashokunbi
New Contributor III

Using IF with a variable in expression

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

1 Solution

Accepted Solutions

Re: Using IF with a variable in expression

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

20 Replies
YoussefBelloum
Esteemed Contributor

Re: Using IF with a variable in expression

Hi,

what is the content of the variable ?

Re: Using IF with a variable in expression

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

ashokunbi
New Contributor III

Re: Using IF with a variable in expression

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
New Contributor III

Re: Using IF with a variable in expression

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


Thanks

dilipranjith
Valued Contributor

Re: Using IF with a variable in expression

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

ashokunbi
New Contributor III

Re: Using IF with a variable in expression

Money

Thanks

dilipranjith
Valued Contributor

Re: Using IF with a variable in expression

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

YoussefBelloum
Esteemed Contributor

Re: Using IF with a variable in expression

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

ashokunbi
New Contributor III

Re: Using IF with a variable in expression

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