Grouping in chart

[mattdaubney]

Hi. Am sure that this is an easy one, just banging my head against a wall at the moment... 😉

We have line level data that has branch, sale date, (which we derive sale month from), Account number, and all of the other line level, eg sales value, supplier etc.

What we want to do is by branch say how many customer purchased in that branch, which we have done by expression (A):- =sum({<[Sales Financial Year]={"$(vCurrentFY)"},[Business Type Include]={'Yes'}>} _mflagSpendingCustomers)   Dimension Branch Name

This is working as expected.

then in another chart for each financial month we have an expression that gets a count of the customers that visited:- =Count({<[Sales Financial Year]={"$(vCurrentFY)"},[Business Type Include]={'Yes'}>} [Customer Account No])  Dimension Sales Month

This looks to be working fine.

Then what we want to do is get the average for each month for each branch.

By dimension Branch I have tried I have the expression from A and an expression =avg(aggr( sum({<[Sales Financial Year]={"$(vCurrentFY)"},[Business Type Include]={'Yes'}>} _mflagSpendingCustomers), [Sales Month]))

This works if I drill into a particular branch, ie the chart shows both bars, one that has the correct count and one that has the correct average, but when no branch is selected I get the count per branch, but an overall average...

I am sure that the issue is in the aggr part of the expression, ie I need to add one or move it about a bit, but....

Any assistance gratefully received.

[shraddha_g]

Did you try,

avg(aggr( sum({<[Sales Financial Year]={"$(vCurrentFY)"},[Business Type Include]={'Yes'}>} _mflagSpendingCustomers), [Sales Month], [Branch Name] ))

[mattdaubney]

Hi. Many thanks. I hadn’t, but I have now and it works.

Many thanks again.

[sunny_talwar]

Your's is a grain mis-match problem... read about this here:

Pitfalls of the Aggr function

What shraddha.g‌ has mentioned should resolve your issue as earlier you were just aggregating on Month, but your requirement was to aggregate on both month and branch

Then what we want to do is get the average for each month for each branch.

So, in order to aggregate over month and branch, you need to add them both in your aggr() function's dimensions

[mattdaubney]

Thanks to Shraddha and Sunny,  Now working.  Will have a read about the pitfalls of aggr.