Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for
Search instead for
Did you mean:
Contributor II

## Adding totals of costs to a different column in the data model

Hello all,

If I have project costs estimated by 2 people, how can I combine them to an existing data model field

data model looks similar to this (load Script, but can be changed)

Program, grouping

Program 1, Group1

Program 1, Group2

Program 1 = Function 1 = 2000

Program 1 = Function 2 = 3000

Program 1 = Function 3 = 4000

Group 1 should consist of Function 1 and Function 3

Group 2 should consist of Function 2

In the expressions, I can assign the cell to Function 1 + Function 3 using if's, sums,....

however I cannot associate the resulting value to the name "Group 1" or "Group 2"

so my result grid shows

Program 1  "-' \$6000

1 Solution

Accepted Solutions
Master II

assuming you have two tables, one table has Group and Program and 2nd table has Program and Function and you want to aggregate them and add the aggregation to the 1st table:

``````table1:
load * inline [
Program, Group
Program 1, Group1
Program 1, Group2
];
NoConcatenate
table2:
load * inline [
Program, Function, Amount
Program 1,Function 1,2000
Program 1,Function 2,3000
Program 1,Function 3,4000
];

left join (table1)
load Program, Group, sum(Amount) as Total
group by Program, Group;;
load Program,
if(Function='Function 2','Group2','Group1') as Group,
Amount
resident table2;``````
6 Replies
Master II

assuming you have two tables, one table has Group and Program and 2nd table has Program and Function and you want to aggregate them and add the aggregation to the 1st table:

``````table1:
load * inline [
Program, Group
Program 1, Group1
Program 1, Group2
];
NoConcatenate
table2:
load * inline [
Program, Function, Amount
Program 1,Function 1,2000
Program 1,Function 2,3000
Program 1,Function 3,4000
];

left join (table1)
load Program, Group, sum(Amount) as Total
group by Program, Group;;
load Program,
if(Function='Function 2','Group2','Group1') as Group,
Amount
resident table2;``````
Master II

so my result grid shows

Program 1  "-' \$6000

didnt you mean Group1 has aggregated value of 6000 and not Program 1?

Contributor II
Author

I should have been more clear.  the resulting grid should show

Program 1      Group 1    \$6000

Program 1      Group 2     \$3000

so what happens is we get costs from lets say materials, labor as well as internal workers

so function 1 = "Material Cost"

Function 2 = "Internal Cost"

Function 3  = "Labor Cost"

in the summary report, I am trying to display the combination of Labor Cost and Material Cost as "External Cost"  this is a parameter which is created in the data model as an inline table

so we need to add Function 1 + Function 3 and display in the External Cost bucket.

I will check out your example now

Thanks

Contributor II
Author

also "External Costs" and "Internal Costs" are 2 values under the Cost Account field (inline table)

These are not separate fields

Master II

the solution is the same you just have to change the field names and values

Group becomes your external and internal costs

Master II

Community Browser