1 is to Many Chart Issue when using Distinct on expression

[#1]

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

[arulsettu]

what are the dimensions using in your chart?

try this

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

[tresesco]

Try:

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

[jonathandienst]

Perhaps you need something like:

    Sum(Distinct TOTAL [PR No])

    or

    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)

[Anil_Babu_Samineni]

Would you be able to provide application.

[#6]

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

[#7]

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