# 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
Did you mean:
Contributor

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

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)
• ### Qlik Sense

2 Replies
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

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,