topic Re: Count top customers over a certain threshold in QlikView

Count top customers over a certain threshold

cicciput — Tue, 14 Jul 2015 11:10:13 GMT

This is tricky...

I have a fact table with sales made by customers, now let's assume:

I know I can sum luxury sales like this:

Sum({< Sales = {">100"} >} Sales)

I know I can count customers with at least 1000€ sales like this:

Sum({< %AccountID = {"=Sum(Sales) > 1000"} >} Account.DCF)

Now what if I want to count customers with at least 1000€ sales of luxury objects?

This does not work...

Sum({< %AccountID = {"=Sum({< Sales = {">100"} >} Sales) > 1000"} >} Account.DCF)

It's worth pointing out this is a What if application so that 1.000 and 100 values above change on user action on 2 sliders.

Thanks for your help!

sunny_talwar — Tue, 14 Jul 2015 11:13:26 GMT

Try this:

Sum({< %AccountID = {"=Sum({<Sales = {'>100'} >} Sales) > 1000"} >} Account.DCF)

jagan — Tue, 14 Jul 2015 11:16:29 GMT

Hi,

You need to use single quotes for >100 in the set analysis.

Sum({< %AccountID = {"=Sum({< Sales = {'>100'} >} Sales) > 1000"} >} Account.DCF)

Regards,

Jagan.

MK_QSL — Tue, 14 Jul 2015 11:16:53 GMT

=SUM({<%AccountID = {"=SUM(Sales)>1000"}>*<Sales = {">100"}>}Account.DCF)

cicciput — Wed, 15 Jul 2015 07:34:13 GMT

Thanks also others who answered too!

Cheers