I am looking at charting profitable and/or unprofitable customers by period and am looking at the best way of doing this. Please see attached with a group of 3 expressions that need to reflect the fact that the Sum(Profit) for that Period for each customer is > 0.
I think your best choice here is to use Set Analysis to limit your scope of customers to Profitable, non-profitable, etc... There is a nice example in the Help article "Set Analysis", one in the bottom where the expression is selecting Customers based on their total Sales. You can do something very similar.
I think it should perform well, even on a large data set, as long as you stay away from IF conditions.
you are right! Set Analysis expressions are evaluated "globally", they can't relate to the individual dimension value (e.g. Month). So, we can report on Customers are are Profitable in general, or in a certain year, but not with relation to the specific month.
I keep forgetting about the fact that Set Analysis expressions cannot be connected to the Dimension values...
Many thanks Oleg / Anatoly there was some interesting discussion points here and as a result i have been able to provide a solution albeit the use of the aggr() and if() functions are giving me a slight performance headache but i'm sure i can work through the document and streamline it some more to minimize the effect to the end users.
I now have a slightly different issue in that i would like to accumulate the customer counts for each month on a rolling 3 month basis as below. However i also want to ensure that the accumulation always starts at Period 1 regardless of selections made, so if i only select Period 4 the answer should still be the accumulation of Periods 2 + 3 + 4
i.e. Period 1 = Period 1 Period 2 = Period 1 + Period 2 Period 3 = Period 1 + Period 2 + Period 3 Period 4 = Period 2 + Period 3 + Period 4
Any thoughts would be welcomed, the inbuilt accumulation mechanism does not disregard selections so i don't think i can use this.