topic Re: Relationship between Distinct and Fields in QlikView

Relationship between Distinct and Fields

tinkerz1 — Wed, 25 Nov 2020 16:16:04 GMT

HI,

I have in a chart the dimensions 'Date' and 'Type' but I also have a further field to group by called 'Area', then I want to count the distinct Salesperson

How do I set the dimension and expression to have 2 Dimensions but be grouping by 3 dimension and perform a count distinct

Thanks

Re: Relationship between Distinct and Fields

sunny_talwar — Tue, 03 May 2016 09:29:32 GMT

May be use Aggr Function:

Sum(Aggr(Count(DISTINCT Measure), Dim1, Dim2, Dim3))

Re: Relationship between Distinct and Fields

Peter_Cammaert — Tue, 03 May 2016 10:21:49 GMT

So for each distinct value of Date and Type, you will get multiple count(DISTINCT Salesperson) values, one for each hidden Area value. What do you want to do with those values? Because in the end they'll have to fit in a single expression result box that only accepts a single value.

IMHO adding them back together makes the third dimension pretty much useless.

Re: Relationship between Distinct and Fields

Peter_Cammaert — Tue, 03 May 2016 10:26:02 GMT

Sorry, there is a way out for this and it is a simple one.

Create a straight table, add the three dimensions and the expression count(DISTINCT Salesperson), and in tab Presentation, hide the third dimension column.

You'll now have multiple rows with the same Date/Type values and different or identical expression results. Because behind the scenes, the third dimension will still force different outcomes on different rows.

Re: Relationship between Distinct and Fields

tinkerz1 — Tue, 03 May 2016 11:24:42 GMT

Thanks to both, I was after the row count and these solutions will both work.

Thanks.