Sum using If but only Distinct

[dmac1971]

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....

[dmac1971]

Sorry should read :

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

[Jason_Michaelides]

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

[preminqlik]

hey dermot try this,

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

[dmac1971]

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

[Jason_Michaelides]

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

[dmac1971]

No sorry that doesnt work, expression error.

[#8]

Hi,

Is it possible to post sample excel data?

[dmac1971]

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

[Jason_Michaelides]

Could you use ID as a dimension in the chart?