topic Profitable & Unprofitable Customers in QlikView

Profitable & Unprofitable Customers

johnhorneramllp — Fri, 09 Apr 2010 06:48:30 GMT

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

Profitable & Unprofitable Customers

sparur — Fri, 09 Apr 2010 07:35:59 GMT

Hello John.

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

Profitable & Unprofitable Customers

johnhorneramllp — Fri, 09 Apr 2010 13:27:15 GMT

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

Profitable & Unprofitable Customers

sparur — Fri, 09 Apr 2010 13:53:08 GMT

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;

Profitable & Unprofitable Customers

Oleg_Troyansky — Fri, 09 Apr 2010 14:26:44 GMT

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,

Profitable & Unprofitable Customers

sparur — Fri, 09 Apr 2010 17:45:03 GMT

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 ?

Profitable & Unprofitable Customers

Oleg_Troyansky — Fri, 09 Apr 2010 21:36:30 GMT

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

Profitable & Unprofitable Customers

johnhorneramllp — Wed, 05 May 2010 04:14:12 GMT

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