Profitable & Unprofitable Customers

[johnhorneramllp]

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.

Any suggestions would be most welcome.

Regards

John H

[sparur]

Hello John.

I create 2 charts (Line and Bar-stack) which show what you looking for. see my example in attachment.

[johnhorneramllp]

Thanks Anatoly, this works great. Is there any other method for doing this with a large dataset that isn't quite such a performance killer?

John

[sparur]

do you have a performance problem? How much rows in your fact table?

You can calculate a new field (in script) with flag 1/0 for profit customer. for example:

tmp:

load

Period, customer,

if(sum(Profit) > 0, 1,0) as IsProfitCustomer

Resident FactTable

Group by Period, Customer

Left Join(FactTable)

LOAD * RESIDENT tmp;

drop table tmp;

[Oleg_Troyansky]

John,

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.

cheers,

[sparur]

Hello, Oleg.

I tried to create something similar (as example in Documentation) but I can't do something right.

for exmple i tried such as:

1) count({< Customer = {"=sum(Profit) > 0"}>} distinct Customer)

in each period I had 3 Customer, but it didn't right. because in 3,6,11 and 12 periods we have unprofitable customer.

I think we need aggregate sum(Profit) by Customer, and i tried such expression:

2) count({< Customer = {"=sum(total<Period, Customer> Profit) > 0"}>} distinct Customer)

but it didn't right too.

Can you post a right expression ?

[Oleg_Troyansky]

Anatoly,

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

[johnhorneramllp]

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.

John H