Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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))
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))
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,
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))
I was wrong..
Gysbert solution is perfect..
I misunderstood it.. 😞