topic Re: ONLY() errors out in #1KPI and Gauge in App Development

ONLY() errors out in #1KPI and Gauge

arusanah — Thu, 23 Dec 2021 18:27:05 GMT

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 ?

Re: ONLY() errors out in #1KPI and Gauge

sunny_talwar — Mon, 17 Dec 2018 15:18:21 GMT

Do you get multiple rows in the chart where this works? and are those values different?

Re: ONLY() errors out in #1KPI and Gauge

arusanah — Mon, 17 Dec 2018 15:28:28 GMT

No I get single the record .

Re: ONLY() errors out in #1KPI and Gauge

sunny_talwar — Mon, 17 Dec 2018 15:24:40 GMT

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))

Re: ONLY() errors out in #1KPI and Gauge

arusanah — Mon, 17 Dec 2018 16:20:51 GMT

Hi Sunny

here is the out come . I need to show only last row in my KPI & Gauge.

Re: ONLY() errors out in #1KPI and Gauge

sunny_talwar — Mon, 17 Dec 2018 16:26:09 GMT

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))

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))

Re: ONLY() errors out in #1KPI and Gauge

arusanah — Mon, 17 Dec 2018 16:44:03 GMT

TY So Much Sunny( my virtual guru :P) . It did work , I see where I was going wrong . I tried sum before but I used in both numerator and denominator and that didn't help.

Till Next Happy Holidays & a very Happy New Year!!!

Re: ONLY() errors out in #1KPI and Gauge

sunny_talwar — Mon, 17 Dec 2018 16:47:43 GMT

Thank you and same to you!!

Best,
Sunny