topic Re: set analysis in QlikView

set analysis

Sat, 01 Dec 2012 20:03:55 GMT

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

=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.

Re: set analysis

swuehl — Sat, 01 Dec 2012 22:45:42 GMT

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)

Re: set analysis

Sun, 02 Dec 2012 00:13:46 GMT

Dear stephen.

Thanks it's working .

Thanks,

Mukram.

Re: set analysis

Sun, 02 Dec 2012 08:05:40 GMT

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.

Re: set analysis

swuehl — Sun, 02 Dec 2012 17:20:18 GMT

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.

Re: set analysis

Sun, 02 Dec 2012 19:14:28 GMT

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.

Re: set analysis

swuehl — Sun, 02 Dec 2012 23:20:53 GMT

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?

Re: set analysis

Mon, 03 Dec 2012 13:02:12 GMT

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.