QlikView Aggregation Calculation, need help

[#1]

Hi Everyone,

I ran into a problem when trying to calculate one of the charts %

So chart is displaying percentages at item level while i also have item group(therapeutic class)  witch is higher aggregation .

Currently it displayed top 5 items  for each therapeutic class .

The problem is that i need to take total amount in that therapeutic class not just for 5 items that are displayed but for all item and display percentage for those 5 items from total for the class . Please see below screenshot of the chart and my attempt to calculate that. Currently it only calculates percentage for one item in each class, but i need to display it for each item.

The code works if i do not do aggr in second part but it doesn't give me desired results . The reason i do aggr in second part is to get total for the whole class not just for those 5 items .

So pretty much its item /total (of the whole class )

Num(

(Sum({($<[CNT_ID]={Y}> + $<[Returns Contract_Indicator]={Y}> + $<Contract_Indicator={Y}>) * $<YearMonthNumber={"$(vCurrPeriodRange)"}>} [MI EXT$]) +

Sum({($<[CNT_ID]={Y}> + $<[Returns Contract_Indicator]={Y}> + $<Contract_Indicator={Y}>) * $<YearMonthNumber={"$(vCurrPeriodRange)"}>} [Returns Net_Amount]) +

Sum({($<[CNT_ID]={Y}> + $<[Returns Contract_Indicator]={Y}> + $<Contract_Indicator={Y}>) * $<YearMonthNumber={"$(vCurrPeriodRange)"}>} CNet_Amount))

/

Aggr(Sum({$<YearMonthNumber={"$(vCurrPeriodRange)"}>}[MI EXT$]) +

Sum({$<YearMonthNumber={"$(vCurrPeriodRange)"}>} [Returns Net_Amount]) +

Sum({$<YearMonthNumber={"$(vCurrPeriodRange)"}>} CNet_Amount),THPY_GRP_CD),$(vFmt%))

On the screenshot you can see that its only calculates percentage for one random item but i need for each item .

In first one you can see its 40% which is correct number .

Its last column's total divided by net purchases .

Thank you in advance!!!!!!!!!!!!!!!!!

[amayuresh]

Please share sample data.

[sunny_talwar · Accepted solution]

Try this:

Num(

(Sum({($<[CNT_ID]={Y}> + $<[Returns Contract_Indicator]={Y}> + $<Contract_Indicator={Y}>) * $<YearMonthNumber={"$(vCurrPeriodRange)"}>} [MI EXT$]) +

Sum({($<[CNT_ID]={Y}> + $<[Returns Contract_Indicator]={Y}> + $<Contract_Indicator={Y}>) * $<YearMonthNumber={"$(vCurrPeriodRange)"}>} [Returns Net_Amount]) +

Sum({($<[CNT_ID]={Y}> + $<[Returns Contract_Indicator]={Y}> + $<Contract_Indicator={Y}>) * $<YearMonthNumber={"$(vCurrPeriodRange)"}>} CNet_Amount))

/

Aggr(NODISTINCT Sum({$<YearMonthNumber={"$(vCurrPeriodRange)"}>}[MI EXT$]) +

Sum({$<YearMonthNumber={"$(vCurrPeriodRange)"}>} [Returns Net_Amount]) +

Sum({$<YearMonthNumber={"$(vCurrPeriodRange)"}>} CNet_Amount),THPY_GRP_CD),$(vFmt%))

[Kushal_Chawda]

I am not sure but give it a try

Num(

(Sum({($<[CNT_ID]={Y}> + $<[Returns Contract_Indicator]={Y}> + $<Contract_Indicator={Y}>) * $<YearMonthNumber={"$(vCurrPeriodRange)"}>} [MI EXT$]) +

Sum({($<[CNT_ID]={Y}> + $<[Returns Contract_Indicator]={Y}> + $<Contract_Indicator={Y}>) * $<YearMonthNumber={"$(vCurrPeriodRange)"}>} [Returns Net_Amount]) +

Sum({($<[CNT_ID]={Y}> + $<[Returns Contract_Indicator]={Y}> + $<Contract_Indicator={Y}>) * $<YearMonthNumber={"$(vCurrPeriodRange)"}>} CNet_Amount))

/

Sum(total <Item> {$<YearMonthNumber={"$(vCurrPeriodRange)"}>}[MI EXT$]) +

Sum(total <Item> {$<YearMonthNumber={"$(vCurrPeriodRange)"}>} [Returns Net_Amount]) +

Sum(total <Item> {$<YearMonthNumber={"$(vCurrPeriodRange)"}>} CNet_Amount),THPY_GRP_CD))

[#5]

Thank you that worked !!!