set analysis

[#1]

Dear Community.

i need a help to write the set analysis.

i want to calculate

1)    count(distinct CustomerCode) where year=2012 and Month=Nov and Sum(Gross Sales) less than are equal to 0

2)     count(distinct CustomerCode) where year=2012 and Month=Nov and Sum(Gross Sales)  in between 1 to 300

3)     count(distinct CustomerCode) where year=2012 and Month=Nov and Sum(Gross Sales) in Between 301 to 700

4)     count(distinct CustomerCode) where year=2012 and Month=Nov and Sum(Gross Sales) More than 701

i tried like below

1)

=Count({<CustomerCode={"=Sum({<Year={'2012'},Month={'Nov'}>}[Gross Sales])<=0"}>} Distinct CustomerCode)

it's working.

but i'm little bit confuse to write the set analysis for  in between 1 to 300   ?

                                                                            in between 301 to 700    ?

=Count({<CustomerCode={"=Sum({<Year={'2012'},Month={'Nov'}>}[Gross Sales])>=701"}>} Distinct CustomerCode)

so please somebody help me.

Thanks,

Mukram.

[swuehl]

I think it should work like this:

=Count({<CustomerCode={"=Sum({<Year={'2012'},Month={'Nov'}>}[Gross Sales])>0 and Sum({<Year={'2012'},Month={'Nov'}>}[Gross Sales])<= 300"}>} Distinct CustomerCode)

=Count({<CustomerCode={"=Sum({<Year={'2012'},Month={'Nov'}>}[Gross Sales])>300 and Sum({<Year={'2012'},Month={'Nov'}>}[Gross Sales])<= 700"}>} Distinct CustomerCode)

[#3]

Dear stephen.

Thanks it's working .

Thanks,

Mukram.

[#4]

Dear Stephen.

i need some help in the above scenario,

Requriement :

Count the distinct customers for the months Aug,Sep,Oct.

=Count({<Month={'Aug','Sep','Oct'}>}Distinct CustomerCode)

from these coustomers i what to calculate how many did how much gross sales in the month of NOV.(same as above 4 conditions)

example:

suppose:

=Count({<Month={'Aug','Sep','Oct'}>}Distinct CustomerCode)            =1000

from these coustomers only i want to know how many did the how much gross sales in the month of  NOV.

with ranges.

MTD>0:

MTD-1-300:

MTD-301-700

MTD<701

Thanks,

Mukram.

[swuehl · Accepted solution]

Maybe like

=Count(

{<CustomerCode = p({<Month={'Aug','Sep','Oct'}>} ) * {"=Sum({<Year={'2012'},Month={'Nov'}>}[Gross Sales])>0 and Sum({<Year={'2012'},Month={'Nov'}>}[Gross Sales])<= 300"}>}

Distinct CustomerCode)

to count the distinct customer that have any relation to Month Aug, Sep, Oct as well as a sum of Gross Sales between 1 and 300 for Nov 2012.

[#6]

Dear Stephen.

Thanks for your help.

but i'm getting the exact result.

total customers from last 3 months equals to 1000.

then all the total customers for the month of NOV  MTD-0,MTD-1-300 ,MTD-301-700 and MTD>701 should be equals to 1000.

but i'm getting  less than 1000. other solution please.

any

Thnaks,

mukram.

[swuehl]

The number of customers that are related to last 3 months is not necessarily the same number of total customers of Nov, it can indeed be a number between zero and your first number. Have you checked this?

[#8]

Dear Stephen.

Your suggestion is correct.Yes some customers are missing in the month of NOV those who are customers in the month of Aug,Sep,Oct.

Thanks for your help.

Thanks,

Mukram.