calculation in pivot expression

Fri, 09 Jan 2015 09:40:29 GMT

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?

Re: calculation in pivot expression

Anonymous — Sat, 10 Jan 2015 11:41:15 GMT

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

topic Re: calculation in pivot expression in QlikView

calculation in pivot expression

Re: calculation in pivot expression