Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor
Contributor

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
Contributor III
Contributor 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))

Contributor
Contributor

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?