Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

wendyharrison
New Contributor II

Should I include the else function to achieve my result?

Hi Everyone,

Please could someone help me with this expression.

I have created this as if there is an entry in the column which is 0.00  then it does not get to be included in the overall total.   This works perfectly just so long as the overall discount is not 0.00.

When it is 0.00 my result just shows blank when it should show the Avg(Cost).

I think I probably need to use the else function but I am struggling to know where I should include it.

Expression

Avg(Cost)

*

Avg(if([Discount %]>0,[Discount %]))

+

Avg(Cost)

Result Should be

Discount%CostResult
9.00%394.86433.91
9.00%401.90441.65
0.00%358.99

358.99

Result Qlik Sense showing

  

Discount%CostResult
9.00%394.86433.91
9.00%401.90441.65
0.00%358.99-

 

Many Thanks

Kind Regards

Wendy

3 Replies
MVP
MVP

Re: Should I include the else function to achieve my result?

The if( ) function can take both a "then" part (which you have used) and an "else" part which is the third parameter.

So if you write:

if( [Discount %] > 0  , [Discount %] , 0 )


It will give 0 in discount if the discount is less or equal to 0. By not including the else part it will return a NULL value which will make the entire calculation into a NULL which displays as a dash in the user interface just like you experience.



MVP
MVP

Re: Should I include the else function to achieve my result?

May be this

RangeSum(Avg(Cost) * Avg(if([Discount %]>0,[Discount %])), Avg(Cost))

wendyharrison
New Contributor II

Re: Should I include the else function to achieve my result?

Hi Petter,

This works perfectly.

Thanks

Kind Regards

Wendy