topic Re: KPI with conditions in App Development

KPI with conditions

dana — Sat, 16 Nov 2024 20:10:49 GMT

Hi,

I need to calculate number of Purchase Orders lines for suppliers which include a condition:

Purchasing amount should be greater than an amount define dynamically by the user for the previous 12 months.

In a straight table, with the supplier key as a dimension it works fine.

IF(sum({<PurchOrder_Line_Flag_Received ={1},PurchOrder_Date = {">=$(vDatePrev12Months) <=$(vMaxCreateDate)"}>}PurchOrder_Line_TotalPriceUSD) > vSupplierMinAmount_USD,
count( distinct PurchOrderLineID_Company),Null())

I want to use this metric in a KPI object. Figured the AGGR function should be used.

Tried the following expression but it doesn't work:

sum(IF(aggr(sum({<PurchOrder_Line_Flag_Received ={1},PurchOrder_Date = {">=$(vDatePrev12Months) <=$(vMaxCreateDate>}PurchOrder_Line_TotalPriceUSD),Key_Supp_Company)> vSupplierMinAmount_USD,
aggr(count distinct Key_PurchOrderLineID_Company),Key_Supp_Company),Null())

Help would be much appreciated!

Re: KPI with conditions

Gysbert_Wassenaar — Thu, 29 Aug 2019 07:26:04 GMT

Try this:

sum(aggr(

, Key_Supp_Company))

Re: KPI with conditions

dana — Thu, 29 Aug 2019 07:38:29 GMT

Hi Gysbert,

Thanks for your prompt supply!

Is the first expression you modified calculated per supplier?

IF(sum({<PurchOrder_Line_Flag_Received ={1},PurchOrder_Date = {">=$(vDatePrev12Months) <=$(vMaxCreateDate)"}>}PurchOrder_Line_TotalPriceUSD) > vSupplierMinAmount_USD,

Re: KPI with conditions

dana — Thu, 29 Aug 2019 07:55:02 GMT

After modifying your suggestion it seems to work correctly:

sum(aggr(
IF(aggr(sum({<PurchOrder_Line_Flag_Received ={1},PurchOrder_Date = {">=$(vDatePrev12Months) <=$(vMaxCreateDate)"}>}PurchOrder_Line_TotalPriceUSD),Key_Supp_Company) > vSupplierMinAmount_USD,
count({<PurchOrder_Line_OTDState = {'On Time Delivery'}>} distinct Key_PurchOrderLineID_Company),Null())
, Key_Supp_Company))

Re: KPI with conditions

dana — Thu, 29 Aug 2019 11:41:12 GMT

I was wrong..

Gysbert solution is perfect..

I misunderstood it.. 😞