Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
bsullivan28
Partner - Contributor
Partner - Contributor

Division of two Sums in Load Script

Hi everyone,

I am having an issue with a calculation that I would like to be done in my data load editor instead of a sheet. For the calculation I need to take the Sum of two fields: Lines and Tenure. Then, I need to divide Sum(Tenure)/Sum(Lines) to get the final calculation.

The process in my data model is

1. Create separate table for Lines and take Sum(lines), grouped by dimensions

2. Create separate table 'OneTime' for Tenure and take Sum(Tenure), grouped by dimension

3. CrossTable on 'OneTime' table and Join with Lines table so that Lines is a separate column (not within 'Metric' field created in CrossTable). 

From this step here I cannot get the proper calculation in the Load Script. I have tried creating a new table, taking sum(OneTime)/sum(Lines), but then summing this in a table creates highly inflated values.

But if I create a table in a sheet with the calculation Sum(OneTime)/Sum(Tenure), the number is correct.

Can someone help me with figuring out how to get this calculation into the Load Script so that in a sheet I can use a calculation like sum($<{Metric = {'Tenure'}}>OneTime)?

 

Step 1: Lines table

Lines:
Load

Dim1,

Dim2,

Dim3,

sum(lines_0) as lines

Resident ProfitTemp
Group By

Dim1,

Dim2,

Dim3;

Step 2: OneTime table

OneTime:
Load

Dim1,

Dim2,

Dim3,

sum(device_tenure_0) as tenure

Resident ProfitTemp
Group By
Dim1,

Dim2,

Dim3;

Step 3: CrossTable & Join

Fact1:
CrossTable(Metric, "OneTime", 14)
Load *

Resident OneTime;
Join(Fact1)
Load *
Resident Lines;

 

Working Calc on Dashboard:

bsullivan28_0-1629393372161.png

 

Labels (3)
0 Replies