topic Re: Sum using If but only Distinct in QlikView

Sum using If but only Distinct

dmac1971 — Fri, 25 Oct 2013 11:41:33 GMT

Having trouble getting my head around the syntax in Qlikview, coming from an excel background. I have the following which works but I want to exclude duplicates :

='Standard £ : ' & sum({<[TYPE]={'AFTERMARKET'}, [TYPE={'STANDARD'}>} [SO Line Value])

Ive got a unique identifier in field [id] but no matter what I try I end up getting an error or a zero. I know I need to use {Distinct [id]} but cant figure where it sits in the expression?

Any help much appreciated....

Re: Sum using If but only Distinct

dmac1971 — Fri, 25 Oct 2013 11:43:46 GMT

Sorry should read :

='Standard £ : ' & sum({<[TYPE]={'AFTERMARKET'}, [TYPE2]={'STANDARD'}>} [SO Line Value])

Re: Sum using If but only Distinct

Jason_Michaelides — Fri, 25 Oct 2013 11:48:18 GMT

Are you saying you have duplicate records in your fact table?

Re: Sum using If but only Distinct

preminqlik — Fri, 25 Oct 2013 11:50:22 GMT

hey dermot try this,

sum(aggr(distinct sum({<[TYPE]={'AFTERMARKET'}, [TYPE={'STANDARD'}>} [SO Line Value]),ID)

Re: Sum using If but only Distinct

dmac1971 — Fri, 25 Oct 2013 11:53:03 GMT

Yes, some open orders have duplicate suppliers, so only need to be counted once.

Re: Sum using If but only Distinct

Jason_Michaelides — Fri, 25 Oct 2013 11:55:45 GMT

You may want to look to pull that out into a dimension table using a composite key. Might improve performance.

Re: Sum using If but only Distinct

dmac1971 — Fri, 25 Oct 2013 12:17:51 GMT

No sorry that doesnt work, expression error.

Re: Sum using If but only Distinct

Fri, 25 Oct 2013 12:23:37 GMT

Hi,

Is it possible to post sample excel data?

Re: Sum using If but only Distinct

dmac1971 — Fri, 25 Oct 2013 12:25:55 GMT

No sorry it isnt, id field is a unique identifier though.

Re: Sum using If but only Distinct

Jason_Michaelides — Fri, 25 Oct 2013 12:28:39 GMT

Could you use ID as a dimension in the chart?

Re: Sum using If but only Distinct

dmac1971 — Fri, 25 Oct 2013 12:34:37 GMT

Sorry as I'm just starting to get my head around Qlikview, can you explain further? The expression is used to populate a text box to give a headline, IE total value of an order type etc.

Re: Sum using If but only Distinct

Jason_Michaelides — Fri, 25 Oct 2013 12:54:53 GMT

I really think you should evaluate your data model and remove duplicate values from the fact table.

Although Prem's solution should work. What error are you getting? When you write the expression in the text box are you getting a syntax error (squiggly red underline)?

Re: Sum using If but only Distinct

dmac1971 — Fri, 25 Oct 2013 13:01:49 GMT

Yes syntax error. I dont want to edit the source file, excel, as my idea is to dump a fresh data file in each week to show backlog etc.

Re: Sum using If but only Distinct

Jason_Michaelides — Fri, 25 Oct 2013 13:06:44 GMT

You're not making this easy Please paste the exact expression in your text box here so we can look at it...

You don't normally have to edit the source file. You just may need to write some script to transform the source file into a sensible data model for QlikView.