topic Count Profitable Customers in QlikView

Count Profitable Customers

johnhorneramllp — Tue, 27 Oct 2009 10:57:47 GMT

I would like to count customers only if they are profitable and am having some difficulty getting this to work as i think it should. The attached file highlights my current problem, Table 4 should show 3 profitable customers for 1/1/2009 and not 5.

Any thoughts would be greatly appreciated.

John H

Count Profitable Customers

Anonymous — Tue, 27 Oct 2009 11:28:17 GMT

Hi John,

Try changing the second expression in Table 4 to:

Count(DISTINCT aggr(If(Sum(Margin) > 0,Customer), Date, Customer))

If I understand the problem you have correctly then we need to calculate the Sum(Margin) per Date and Customer and add these up instead of just looking at the Date as all customers had transactions that date so the count will be 5 if the Sum(Margin)>0 for the whole lot of them. Hope this helps.

Count Profitable Customers

johnhorneramllp — Tue, 27 Oct 2009 11:38:17 GMT

Yes it does, thanks Johannes.

John H

Count Profitable Customers

johnhorneramllp — Tue, 27 Oct 2009 11:47:05 GMT

Could this also be done using set analysis and would this be less expensive from a performance perspective?

John H

Count Profitable Customers

johnhorneramllp — Tue, 27 Oct 2009 21:35:38 GMT

Hi all, thanks to Johannes last night i managed to get a solution for my profitable customers. However the use of the below statement is proving too slow to implement. Given the many discussions surrounding performance and Set Analysis on this Forum i am suspecting that using Set Analysis may be a more efficient answer...can the below be translated into a Set Analysis expression? I know i could create an aggregated table of the data i require for the specific chart but i would like the chart to be dynamic with the whole of the data model, so that if a user selects a product the chart should then show profitable customers for that product.

Sum(aggr(If(Sum([Pocket Margin]) > 0, [Customer Counter]), [Fiscal Month], [Customer ID]))

Much appreciated.

John H