Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
rkspareek1992
Partner - Creator
Partner - Creator

Total and group by function

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
1001100109100110010113342065.812725.3061068521.1297826
1001100109100110010213342065.8130279.69711871876.423387
1001100109100110010313342065.811024.471401668.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
1001100109100110010115896.4336361212004.392287382.54
1001100109100110010215727.80403993710080.8625686594.73
10011001091001100103193363.2713668559502.4470688.50

The Red colored values are by group by function.

1 Reply
sunny_talwar

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;