Should I include the else function to achieve my result?

[Anonymous]

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%	Cost	Result
9.00%	394.86	433.91
9.00%	401.90	441.65
0.00%	358.99	358.99

Result Qlik Sense showing

Discount%	Cost	Result
9.00%	394.86	433.91
9.00%	401.90	441.65
0.00%	358.99	-

Many Thanks

Kind Regards

Wendy

[petter]

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.

[sunny_talwar]

May be this

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

[Anonymous]

Hi Petter,

This works perfectly.

Thanks

Kind Regards

Wendy