Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi ,
I got a data set as shown below, (The data set loaded to qlik sense app)
Transaction Date | Division_ID | Service Hours | Actual Cost |
2/04/2018 | A | 100 | |
15/04/2018 | B | 120 | |
20/04/2018 | C | 110 | |
25/04/2018 | D | 130 | |
1/05/2018 | A | 115 | 15000 |
10/05/2018 | B | 120 | 17000 |
12/05/2018 | C | 110 | 13500 |
1/05/2018 | D | 100 | 12000 |
5/06/2018 | A | 105 | 11000 |
15/06/2018 | B | 135 | 20000 |
5/06/2018 | C | 125 | 18000 |
25/06/2018 | D | 140 | 22500 |
15/07/2018 | A | 150 | |
17/07/2018 | B | 125 | |
10/07/2018 | C | 130 | |
1/07/2018 | D | 40 |
Output table:I want to compare the service hours against the actual Cost for each division. The output should looks as below,
Division_ID | Service Hours | Actual Cost |
A | 220 | 26000 |
B | 255 | 37000 |
C | 235 | 31500 |
D | 240 | 34500 |
Thus, the sum of each division's service hours and actual costs required to be calculated.
Note
The sum of Service hours needed to be calculated only if a respective actual cost is available.
I tried using Aggr function and other simple expressions such as,
If([Actual Cost]>0, Sum([Service Hours])) and Aggr(If(Sum([Actual Cost]))>0,[Service Hours]).
But none worked. Could anyone please help me with this regard?
Thanking you.
Kind regards,
Andy
Hi Andy,
You can create in your data model a new field based on "Service Hours" that take value only if "[Actual Cost] > 0":
LOAD Service Hours,
Actual Cost
if([Actual Cost] > 0 ,[Service Hours],0) as [Service Hours with Actual Cost]
...
then your expression is:
Sum([Service Hours with Actual Cost])
Try using Sum(If(.... instead of If(..Sum(..
Straight table:
Dim 1: Division_ID
Exp 1: Sum(If([Actual Cost]>0, [Service Hours]))
Exp 2: Sum([Actual Cost])
You might have to suppress the null values in dimension as well.
Hi Tresesco,
Thanks for the reply. Actually, my real data set is a different and a large one.
Apparently I have tried the method you said also where I have got the correct answer but have failed to select the entire data set when performing the manual testing.
Really, sorry about that.
Anyway, thank you again.
Kind regards,
Andy
Hi Andy,
You can create in your data model a new field based on "Service Hours" that take value only if "[Actual Cost] > 0":
LOAD Service Hours,
Actual Cost
if([Actual Cost] > 0 ,[Service Hours],0) as [Service Hours with Actual Cost]
...
then your expression is:
Sum([Service Hours with Actual Cost])
Hi Jose,
I really like the method you have approached the issue.
Yes, it works for the given data set, however, it does not work accurately when there are "0" values for the "Service Hours".
Thanks.
Regards,
Andy