Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi ,
I am using below expression in Charts and it works fine as expected , but if I use this expression in #1KPI it returns nothing . and for gauge it returns me the correct numbers but wont let me set gauge colors .
ONLY(if([%KPI_PLANT]='A' and KPI_TYPE = 'Manual' and KPI_Month_End = DATE AND Ind='Y',Generic_NUM,0)
+if([%KPI_PLANT]='A' and KPI_TYPE = 'Actual' and DATE <= END_DT and DATE >= START_DT,Generic_NUM,0))
/
ONLY(if([%KPI_PLANT]='A' and KPI_TYPE = 'Actual' and DATE <= END_DT and DATE >= START_DT and ORD_DEN >0,ORD_DEN, 1))
Any Thoughts why only() works differently and how I can make it work ?
Do you get multiple rows in the chart where this works? and are those values different?
No I get single the record .
Can you try them as two separate expressions in the chart and see if you still see a single row for both the expressions?
ONLY(if([%KPI_PLANT]='A' and KPI_TYPE = 'Manual' and KPI_Month_End = DATE AND Ind='Y',Generic_NUM,0) +if([%KPI_PLANT]='A' and KPI_TYPE = 'Actual' and DATE <= END_DT and DATE >= START_DT,Generic_NUM,0))
ONLY(if([%KPI_PLANT]='A' and KPI_TYPE = 'Actual' and DATE <= END_DT and DATE >= START_DT and ORD_DEN >0,ORD_DEN, 1))
Hi Sunny
here is the out come . I need to show only last row in my KPI & Gauge.
So, you numerator seems to be the problem here... may be you can try one of these
Max(If([%KPI_PLANT] = 'A' and KPI_TYPE = 'Manual' and KPI_Month_End = DATE and Ind = 'Y', Generic_NUM, 0) +If([%KPI_PLANT] = 'A' and KPI_TYPE = 'Actual' and DATE <= END_DT and DATE >= START_DT, Generic_NUM, 0)) / Only(If([%KPI_PLANT] = 'A' and KPI_TYPE = 'Actual' and DATE <= END_DT and DATE >= START_DT and ORD_DEN > 0, ORD_DEN, 1))
or
Sum(if([%KPI_PLANT]='A' and KPI_TYPE = 'Manual' and KPI_Month_End = DATE AND Ind='Y',Generic_NUM,0) +if([%KPI_PLANT]='A' and KPI_TYPE = 'Actual' and DATE <= END_DT and DATE >= START_DT,Generic_NUM,0)) / ONLY(if([%KPI_PLANT]='A' and KPI_TYPE = 'Actual' and DATE <= END_DT and DATE >= START_DT and ORD_DEN >0,ORD_DEN, 1))
Thank you and same to you!!
Best,
Sunny