Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
May be this
RangeSum(Avg(Cost) * Avg(if([Discount %]>0,[Discount %])), Avg(Cost))
Hi Petter,
This works perfectly.
Thanks
Kind Regards
Wendy