4 Replies Latest reply: Oct 11, 2017 8:56 AM by Matt Daubney RSS

    Grouping in chart

    Matt Daubney

      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.