Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
johnhorneramllp
Partner - Contributor III
Partner - Contributor III

Profitable & Unprofitable Customers

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

7 Replies
sparur
Specialist II
Specialist II

Hello John.

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

johnhorneramllp
Partner - Contributor III
Partner - Contributor III
Author

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
Specialist II
Specialist II

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Specialist II
Specialist II

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Partner - Contributor III
Partner - Contributor III
Author

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