Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
rrrggg20
Contributor II
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
edwin
Specialist III
Specialist III

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;

View solution in original post

6 Replies
edwin
Specialist III
Specialist III

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;

View solution in original post

edwin
Specialist III
Specialist III

 

so my result grid shows

Program 1  "-' $6000

 

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

rrrggg20
Contributor II
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 

rrrggg20
Contributor II
Contributor II
Author

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

These are not separate fields

 

edwin
Specialist III
Specialist III

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

edwin_0-1608683301934.png

 

Group becomes your external and internal costs

edwin
Specialist III
Specialist III

edwin_1-1608683665705.png