Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a simple KPI chart object and I have successfully populated it using the following expression:
=Sum({<Type={'Monthly'}, Category={'SUB'}, [BusinessDate.autoCalendar.Date]={'30 Apr 2019'}>}Value)/1000
Great! I can see 5 or so separate boxes all with the correct values for the corresponding dimension.
Now all I want to do is make the number shown green when positive and red when negative. So just as a test I've done this:
=if(1>0, '#00ff00', '#ff0000') which makes everything green (obviously because 1 is always greater than 0)
So next I replace my silly 1>0 expression with my original expression above:
=if((Sum({<Type={'Monthly'}, Category={'SUB'}, [BusinessDate.autoCalendar.Date]={'30 Apr 2019'}>}Value)/1000) > 0, '#00ff00', '#ff0000')
I definitely have positive and negative values - why doesn't this work?
Hi Senor,
Conditional formatting in simple kpi works perfect until you have two or more dimension values. But it doesn't work for multi dimensional situations. I think it's a bug or something.
If you find any workarounds for this, please let me know 🙂
Sensor,
I have done multiple field/filtering conditions to set color successfully in qlik sense so you should be able to do it also and I suspect that it is something specific with your set statement.
here is an example from my system that works:
=if(
Sum(
{<[Combined.Type]={"Supply Plan"},[Combined.Status]={"Ordered","Shipped","Received"}>}[Combined.Quantity]) <>
(
Sum(
{<[Combined.Type]={"Order"},[Combined.Status]={"Confirmed","Confirmed - New/Update","Partially Shipped"}>}[Combined.Quantity])
+ Sum({<[Combined.Type]={"Shipment"},[Combined.Status]={"Shipped","Shipped Un-Referenced","Delivered"}>}[Combined.Quantity])
+ Sum({<[Combined.Type]={"Shipment"},[Combined.Status]={"Received"}>}[Combined.Quantity])
)
, 'red' )
In looking at your statement - the first thing that jumps out is the date comparison - you may need to adjust the value you are comparing for to match with the format that the date is stored in. The second is that the leading "(" looks like it might be misplaced.
This change worked in my system using your structure:
=if(
(
Sum(
{<[SupplyPlan.Measure]={"Forecast"}, [SupplyPlan.Consumption Flag]={"1"}, [SupplyPlan.Date]={"1/1/2018"}>} [SupplyPlan.Quantity]
)
/ 1000
) > 0, '#00ff00', '#ff0000'
)
Best of luck.