topic Re: Most common value in chart expression in QlikView

Most common value in chart expression

Fri, 12 Jun 2015 15:58:29 GMT

Hi all, I'm a bit stuck with a chart expression. I want to show the most common branch that a customer has purchased from. If I select a specific customer account number (this is my dimension) then this expression works fine, but It will not work if I don't select a customer

firstsortedvalue(UNIT.BRANCH_NAME, -Aggr(count(UNIT.TOTALSOLD), UNIT.BRANCH_NAME),1)

I'd be very grateful for any help, thanks, Simon

Re: Most common value in chart expression

swuehl — Fri, 12 Jun 2015 16:12:00 GMT

Maybe something like

=Mode( aggr(

firstsortedvalue(UNIT.BRANCH_NAME, -Aggr(count(UNIT.TOTALSOLD),UNIT.BRANCH_NAME),1)

,[Customer account])

)

Re: Most common value in chart expression

Fri, 12 Jun 2015 16:27:53 GMT

Hi swuehl, thanks for your reply, unfortunately that returns null values too

Re: Most common value in chart expression

swuehl — Fri, 12 Jun 2015 16:32:07 GMT

Not really what you want to achieve, could you upload a small sample QVW or describe your chart and data a bit more detailed.

Try also adding the chart dimension to your aggr() dimensions:

firstsortedvalue(UNIT.BRANCH_NAME, -Aggr(count(UNIT.TOTALSOLD),Customer, UNIT.BRANCH_NAME),1)

Re: Most common value in chart expression

swuehl — Sat, 13 Jun 2015 09:30:20 GMT

I think you might run into issues when you don't have a single #1 branch per customer, a situation where QV's FirstSortedValue() function will return NULL.

You can try to break the ties by manipulating the sort order.

Have a look at this sample to see what I mean (you'll need to reload some times to see the ties showing up).

Re: Most common value in chart expression

Mon, 15 Jun 2015 08:07:13 GMT

Hi swuehl, adding chart dimension fixed this, many thanks