Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi i have a pivot table with three dimensions
textvalue, salesis, itemid
the first expression is
value for cumulative year
=SUM({<TCMSALESORDERYear={'$(vMaxYearOrder)'},TCMSALESORDERDATE={'<=$(vMaxDateOrder)'},TCMSALESORDERMonth=,TCMSALESORDERDay=,TCMSALESORDERMonthYear=>} QTYORDERED)
the second expression is
the costprice for cumulative year
=SUM({<TCMSALESORDERYear={'$(vMaxYearOrder)'},TCMSALESORDERDATE={'<=$(vMaxDateOrder)'},TCMSALESORDERMonth=,TCMSALESORDERDay=,TCMSALESORDERMonthYear=>} Aggr((FirstSortedValue(COSTPRICE,-MODIFIEDDATETIME_INVENTTABLEMODUL)*sum(QTYORDERED)), TEXTVALUE,SALESID,ITEMID))
my problem is here
the value for the selected month this expression is right
=SUM({<TCMSALESORDERYear={'$(vMaxYearOrder)'},TCMSALESORDERMonth={'$(vMaxMonthOrder)'},TCMSALESORDERDay={'<=$(vMaxDayOrder)'},TCMSALESORDERDATE=,TCMSALESORDERMonthYear=>}QTYORDERED)
but her is the devil inside:
i get in sum the same like zhe second expression above the result is the cumulative value * costprice and not the month value per costprice
CostPrice per month expression:
sum(aggr(FirstSortedValue(COSTPRICE,-MODIFIEDDATETIME_INVENTTABLEMODUL) * SUM({<TCMSALESORDERYear={'$(vMaxYearOrder)'},TCMSALESORDERMonth={'$(vMaxMonthOrder)'},TCMSALESORDERDay={'<=$(vMaxDayOrder)'},TCMSALESORDERDATE=,TCMSALESORDERMonthYear=>}QTYORDERED),TEXTVALUE, SALESID, ITEMID))
can anybody help?
Is there not a problem with Aggr function?
Try this:
=SUM({<TCMSALESORDERYear={'$(vMaxYearOrder)'},TCMSALESORDERMonth={'$(vMaxMonthOrder)'},TCMSALESORDERDay='<=$(vMaxDayOrder)'},TCMSALESORDERDATE=,
TCMSALESORDERMonthYear=>}
Aggr((FirstSortedValue(COSTPRICE,
-MODIFIEDDATETIME_INVENTTABLEMODUL)*sum(QTYORDERED)),TEXTVALUE,SALESID,ITEMID))