topic Re: Aggregation issue in Straight Table/Pivot Table in QlikView

Aggregation issue in Straight Table/Pivot Table

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have an particular requirement where i am trying to aggregate the data based on an One dimension. There are two dimensions in Total for the same graph. The Sum of the Values is getting calculated properly for this Year and Last Year. But i need to calculate the sum based on division as well and divide the individual numbers by this cumulative sum to get the percentage.

i.e for instance Last Year Under Division A say i am getting 20 for Container A and 40 for Container B and 40 for Container C then i need to get 20 % for A's Share and 40% for B's Share and 40 % for C's Share. But the Problem is that the Aggregation is not happening properly under Division Alone. I need to specify the ContainerType as well which will be common for Division B and Division C as well

Please Note : The First Dimension is DivisionNaming(based on which Aggregation Should Happen) and the second dimension is ContSize. If i remove ContSize it is correct. But need that dimension as well

Attached an Sample Excel.

The Formula that i am using is below

((Sum({<FinYear={'$(=MAX(FinYear)-1)'},originalCostingDate= {">=$(=MonthStart(today()-365))<=$(=today()-365)"},ContType=,ContSize=>} LineTEU))/

Sum(Aggr(Sum({<FinYear={'$(=MAX(FinYear)-1)'},originalCostingDate= {">=$(=MonthStart(today()-365))<=$(=today()-365)"},ContType=,ContSize=>}TOTAL LineTEU),DivisionNaming1))

)

Thanks in Advance for the help.

Thanks and Regards,

Vigneshwaran.M.K

Re: Aggregation issue in Straight Table/Pivot Table

YoussefBelloum — Fri, 20 Apr 2018 08:10:04 GMT

Hi,

I can't find the Measure_field used on your expression and the dimension name used in the aggr() function above in the file you have attached:

Measure field :LineTEU

Dimension: DivisionNaming1

Re: Aggregation issue in Straight Table/Pivot Table

Anonymous — Fri, 20 Apr 2018 09:36:13 GMT

Hi Youssef,

Thanks For your Email. Yes both are correct . Also i am using Second Dimensions as ContSize.

The Problem is that there are three divisions and all three divisions will have all the three Cont Size.

So this % Share column should be the Sum of the Value for that Row / Sum of the value for that whole Division

So i am trying to so the same by giving the numerator as Sum and Denominator as Aggr(Sum(),Division).

The Problem is that Since we have another Value in Dimension it is not happening Properly. But i need the Second Dimension as well

Re: Aggregation issue in Straight Table/Pivot Table

YoussefBelloum — Fri, 20 Apr 2018 09:47:42 GMT

I think you misunderstood me

in your attached file, where is the field: LineTEU ??

you're making the sum on that field

Re: Aggregation issue in Straight Table/Pivot Table

Anonymous — Fri, 20 Apr 2018 10:55:09 GMT

Hi vigneshwaran,

Try the below expressions

((Sum({<FinYear={'$(=MAX(FinYear)-1)'},originalCostingDate= {">=$(=MonthStart(today()-365))<=$(=today()-365)"},ContType=,ContSize=>} LineTEU))/

((Sum({<FinYear={'$(=MAX(FinYear)-1)'},originalCostingDate= {">=$(=MonthStart(today()-365))<=$(=today()-365)"},ContType=,ContSize=>} Total<DivisionNaming1>LineTEU))

)

Basically for numerator you want to total as per the Division naming field.In total you could pass the fields from the chart which should only be considered for calculation.

Hope this helps.