topic QlikView Aggregation Calculation, need help in QlikView

QlikView Aggregation Calculation, need help

Fri, 18 Mar 2016 13:11:25 GMT

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

Re: QlikView Aggregation Calculation, need help

amayuresh — Mon, 21 Mar 2016 09:12:48 GMT

Please share sample data.

Re: QlikView Aggregation Calculation, need help

sunny_talwar — Mon, 21 Mar 2016 09:14:28 GMT

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

Re: QlikView Aggregation Calculation, need help

Kushal_Chawda — Mon, 21 Mar 2016 09:22:48 GMT

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

Re: QlikView Aggregation Calculation, need help

Mon, 21 Mar 2016 13:25:42 GMT

Thank you that worked !!!