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

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
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;