Count top customers over a certain threshold

[cicciput]

This is tricky...

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

• a top customer has sum of sales > 1.000€
• a luxury sale is a sale > 100€

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 · Accepted solution]

Try this:

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

[jagan]

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]

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

[cicciput]

Thanks sunindia!

Thanks also others who answered too!

Cheers