KPI with conditions

[dana]

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!

[Gysbert_Wassenaar · Accepted solution]

Try this:

sum(aggr(

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

, Key_Supp_Company))

[dana]

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, 

[dana]

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

[dana]

I was wrong..

Gysbert solution is perfect..

I misunderstood it.. 😞