Most common value in chart expression

[#1]

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

[swuehl]

Maybe something like

=Mode( aggr(

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

,[Customer account])

)

[#3]

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

[swuehl · Accepted solution]

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)

[swuehl]

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

[#6]

Hi swuehl, adding chart dimension fixed this, many thanks