topic Re: 1 is to Many Chart Issue when using Distinct on expression in QlikView

1 is to Many Chart Issue when using Distinct on expression

Wed, 25 Nov 2020 16:16:04 GMT

Hi Qv Community,

I used an aggregation and distinct on an variable which give me the right figures I wanted but when I plot it on the table as an expression

with the document numbers, it shows a misleading result. please see result below.

The Correct PR Unit figure is 1500, I want my output to be like the one below

Expression is : Sum(aggr(Sum(Distinct [PR Unit]), [PR No], [Item Number]))

Thanks!

Regards,

Charles

Re: 1 is to Many Chart Issue when using Distinct on expression

arulsettu — Fri, 09 Sep 2016 04:23:10 GMT

what are the dimensions using in your chart?

try this

aggr(Sum(Distinct [PR Unit]), [PR No], [PO No])

Re: 1 is to Many Chart Issue when using Distinct on expression

tresB — Fri, 09 Sep 2016 04:29:33 GMT

Try:

Sum(aggr(NoDistinct Sum(Distinct [PR Unit]), [PR No], [Item Number]))

Re: 1 is to Many Chart Issue when using Distinct on expression

jonathandienst — Fri, 09 Sep 2016 05:00:27 GMT

Perhaps you need something like:

Sum(Distinct TOTAL [PR No])

Sum(Distinct TOTAL<[PO No]> [PR No])

If you need the Distinct to apply at a lower level, then you can use Aggr(), but you need to include ALL the chart dimensions as dimensions for your Aggr(), in addition to any dimensions the expression requires. But this can affect the result for distinct if you use too many dimensions. You might need something like this to fix that:

Sum(TOTAL<[PR No]> Aggr(Sum(Distinct [PR Unit]), [PR No], [Item Number], [PO No]))

(assuming the values in the PR Unit column are totals for a PR No)

Re: 1 is to Many Chart Issue when using Distinct on expression

Anil_Babu_Samineni — Fri, 09 Sep 2016 05:26:57 GMT

Would you be able to provide application.

Re: 1 is to Many Chart Issue when using Distinct on expression

Fri, 09 Sep 2016 07:12:38 GMT

I use dynamic dimensions so a lot of field can be added as dimension on my chart

Re: 1 is to Many Chart Issue when using Distinct on expression

Fri, 09 Sep 2016 07:15:31 GMT

the red rows indicates that the Document is deleted, can I have this output instead?