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

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Yean
Contributor II
Contributor II

Using Distinct for two fields in Set Analysis together with Sum function

Yean_1-1600832253710.png

 

Hello ! I have a set of data similar like the picture above. 

(i) The same PlanID and PlanFrame have different DowntimeReason.

(ii)The same PlanID but different PlanFrame have different  DowntimeReason.

I need to sum up all the Timespend and divide with sum of TotalRunTime & TotalDownTime, meaning sum(Timespend)/ (sum(TotalRunTime)+sum(TotalDownTime)). But the difficult part is i need to ignore all the rows where NcQty is negative and at the same time,  i can only add the TotalRunTime and TotalDownTime where PlanId  and PlanFrame is distinct value. If PlanId is the same but PlanFrame is different, then i need to add up too. So for the picture above, i need to get sum(Timespend) / ((8722+6542+3325+6541+1213+5429) + (799+142+350+450+540+225)).

So anyone have any idea how to write the expression for this? For now, what I did is as below:

=(Sum({$<[NcQty]-={"<0"}>}[Timespend]))
/(Sum({$<[NcQty]-={"<0"}>}[TotalRunTime]) + Sum({$<[NcQty]-={"<0"}>}[TotalDowntime]))

This expression only fulfilled the ignoring NcQty which is negative but doesn't fulfilled the distinct planId and planframe. Can anyone tell me how to do it? Thanks in advance ! 

Labels (1)
2 Replies
G3S
Creator III
Creator III

So for the picture above, i need to get sum(Timespend) / ((8722+6542+3325+6541+1213+5429) + (799+142+350+450+540+225)).

Firstly, in your post, since NcQty is -7, shouldn't PlanID456 with PlanFrame2  be excluded (in orange)?. 

If so, required result: 30603  (total run time = 28447 + total downtime = 2156)

 

try this:

Create a concatenated key of PlanID&PlanFrame as Key. 

expression:

Sum(Aggr(Sum(DISTINCT {$<[NcQty]-={"<0"}>} [TotalRunTime]), Key))
+
Sum(Aggr(Sum(DISTINCT {$<[NcQty]-={"<0"}>} [TotalDownTime]), Key))

Yean
Contributor II
Contributor II
Author

Sorry, it's my mistake, the orange number should be excluded. I tried your way and it's brilliant ! I got the visualization that I want. Btw, I have another question. If I wan to get the exact same thing for previous month, how should i type the expression? This is what I did, 

sum(aggr(sum(DISTINCT{$<[DateValue.autoCalendar.Month]={"$(=Month(AddMonths(max([DateValue]),-1)))"},[NcQty]-={"<0"}>}[TotalRunTime]),[Key]))

+

sum(aggr(sum(DISTINCT{$<[DateValue.autoCalendar.Month]={"$(=Month(AddMonths(max([DateValue]),-1)))"},[NcQty]-={"<0"}>}[TotalDowntime]),[Key]))

The graph didn't show anything, but previously without the Distinct issue, I used the same expression and the visualization that I got for previous month is correct. Is it that i cannot combine both the previous month and NcQty condition in set analysis like what i did?