Total and group by function

rkspareek1992 — Wed, 01 Mar 2017 08:51:45 GMT

Hi,

I want to total of some values with respect to dimension. So in front end I am using TOTAL function, but I want to total the values with respect to dimension at script level. As we cannot use TOTAL function in Script. So I am using group by function.

The issue with group by function is that: I have about to 70 fields and when I am using group by function with all of the fields then server going in hang situation.

When I am using group by function wrt 3-4 dimentions/fields then I cannot explore my data wrt other dimensions.

So kindly suggest any other method.

Below is my output from front end:

COEP_Cost_Center	Allocation	Sum(Cost_INR)	sum(distinct ProdQty)	Sum(Cost_INR)*sum(distinct ProdQty)/sum(total<COEP_Cost_Center>ProdQty)
		40026197.43	34029.474
1001100109	1001100101	13342065.81	2725.306	1068521.1297826
1001100109	1001100102	13342065.81	30279.697	11871876.423387
1001100109	1001100103	13342065.81	1024.471	401668.25683043

Below is the script and output:

COEP3:

LOAD

COEP_Cost_Center,

Allocation,

Sum(Cost_INR)*sum(distinct ProdQty) as Qty1,

sum(ProdQty) as Qty2

Resident COEP2

Group by COEP_Cost_Center,

Allocation;

DROP Table COEP2;

COEP_Cost_Center	Allocation	Sum(Qty1)/Sum(Qty2)	Sum(Qty1)	Sum(Qty2)
		16189.30	454023481587.68	28044665.77
1001100109	1001100101	15896.43	36361212004.39	2287382.54
1001100109	1001100102	15727.80	403993710080.86	25686594.73
1001100109	1001100103	193363.27	13668559502.44	70688.50

The Red colored values are by group by function.

Re: Total and group by function

sunny_talwar — Wed, 01 Mar 2017 09:33:22 GMT

Seems like this will have to be a two step process

COEP3:

LOAD

COEP_Cost_Center,

Allocation,

Sum(Cost_INR)*sum(distinct ProdQty) as Qty1

Resident COEP2

Group by COEP_Cost_Center, Allocation;

Left Join (COEP3)

LOAD COEP_Cost_Center,

sum(ProdQty) as Qty2

Resident COEP2

Group By COEP_Cost_Center;

DROP Table COEP2;

topic Re: Total and group by function in QlikView

Total and group by function

Re: Total and group by function