topic Re: Sum distinct? in QlikView

Sum distinct?

Anonymous — Fri, 23 Jun 2017 12:27:44 GMT

Hi,

Is it possible to create a sum distinct?

I have multiple items related to an order, and I will like to sum the orders just once.

How can I do that?

Re: Sum distinct?

Frank_Hartmann — Fri, 23 Jun 2017 12:36:41 GMT

what about

sum(distinct Field)

Re: Sum distinct?

Anonymous — Fri, 23 Jun 2017 12:40:57 GMT

Thank you. I didn't know that I can use the word and that's it.

However, this is the expression that I have:

SUM({$<[Escalations_QV.Escalated State] -= {"Pre-Escalation", "Invalid Escalation"}, [Escalations_QV.Closure Code] -= {"Not an Escalation","Transferred to Duty Manager","Transferred to At Risk"}>} [Escalations_QV.Escalated Case Count])

I need to add distinct by the ID. How can I do it in this expression?

Re: Sum distinct?

Frank_Hartmann — Fri, 23 Jun 2017 12:43:18 GMT

i cannot see any ID field?

maybe:

Re: Sum distinct?

Anonymous — Fri, 23 Jun 2017 12:47:35 GMT

you're right, in the expression there is not the ID field, however I will like to add it, because if I use the distinct on Escalated Case Count as this is 1 or 0, it will only count it once.

I will like to add the ID in any place in the expression, to use the distinct. Any idea?

Re: Sum distinct?

Frank_Hartmann — Fri, 23 Jun 2017 14:07:50 GMT

not sure if i correctly understood your requirement.

how do you know which ordervalue per ID is the correct one to pick?

Can u provide an detailed example and a sample qvw to play with?

Re: Sum distinct?

Peter_Cammaert — Fri, 23 Jun 2017 14:17:18 GMT

As far as I can see, you want to fix something that seems broken from the start.

Why do you duplicate your values? Can you not keep them distinct in an Orders table, and put all items in a linked table where they can do no harm? Or drop the Order total and sum() it each time from the parts? Sum() is awfully fast.