how to determine how many customers are under and over a $ value

[#1]

Hi, I am attempting to report at different summary levels (area / district / country) how many customers have purchased under 75%, between 75 and 110% and over 110% of a preset value. I don't need or want to have to list all the customers individually.

any help  / hints would be appreciated!

[#2]

Hi, I have added a data file to the original post. You can see the data file is a concatenated file of Sales and Planned.

To restate the question, in simple terms:

How can I report the number of Customers, be territory  whose total sales are :

1. below Planned YTD

2. = Planned YTD

3. above Planned YTD

thanks!

[MK_QSL]

Let me clarify before starting on your file.

You want number of customers where

1) SUM([Actual Sale]) = SUM([Planned YTD])

2) SUM([Actual Sale]) < SUM([Planned YTD])

3) SUM([Actual Sale]) > SUM([Planned YTD])

[#4]

correct, for each territory.

thanks

[MK_QSL]

Please check enclosed file...

[#6]

Hi Manish, it looks like you added 'Flag' to the data file?  That cant work for me, as it needs to be sensitive to the selections of the user's selections of time and products selected.

[Anonymous]

Take a look at the attached, hope it helps...

[MK_QSL · Accepted solution]

You can use the same as calculated dimension in UI side

[#9]

many thanks Manish, and Michael, I am on the right path now.

[MK_QSL]

Glad that it sorted out your problem...

Jut one thing to explain....

I have used below in script

NUM(Territory_ID) as Territory_ID, 

instead of Territory_ID because, the excel file you have provided having some Territory_ID in number format and some in string...!!