Pie chart with no dimension

[#1]

Hi,

I wonder if somebody can help me with a couple of expressions that are driving me mad.

I have a list of Clients to which I have assigned a certain Status (Green, Yellow and Red) that is calculated based on the user selections. For each Status I have a list box. Now I have to build a pie chart that shows how many Clients belong to each Status and another one that shows the sum of sales that was made to the Clients belonging to each Status. I guess my pie charts will need three expressions each and no dimensions but I don't seem to be able to make it work.

Below are the expressions that I've used to build each of the three list boxes.

Thanks in advance for your help!

GREEN:

aggr(

if(count(distinct Partner)=1

or

count(distinct Partner)=2   

AND

count(distinct Year)>1

AND

only(aggr(if(rank(aggr(sum({<Year= {$(=Max(Year)-2)}>} Sales),Client,Partner))=1,Partner),Client,Partner))

=

only(aggr(if(rank(aggr(sum({<Year= {$(=Max(Year)-1)}>} Sales),Client,Partner))=1,Partner),Client,Partner))

or

count(distinct Partner)=2

AND

count(distinct Year)>1

AND

only(aggr(if(rank(aggr(sum({<Year= {$(=Max(Year)-2)}>} Sales),Client,Partner))=1,Partner),Client,Partner))

=

if(sum({<Year={$(=Max(Year))}>}Sales)>0, aggr(if(rank(aggr(sum({<Year={$(=Max(Year))}>} Sales),$(Variable1)))=1,Partner),$(Variable1)), aggr(if(rank(aggr(sum({<Year={$(=Max(Year)-1)}>} Sales),$(Variable1)))=1,Partner),$(Variable1)))

or

count(distinct Partner)=2

AND

count(distinct Year)>1

AND

only(aggr(if(rank(aggr(sum({<Year= {$(=Max(Year)-1)}>} Sales),Client,Partner))=1,Partner),Client,Partner))

=

if(sum({<Year={$(=Max(Year))}>}Sales)>0, aggr(if(rank(aggr(sum({<Year={$(=Max(Year))}>} Sales),$(Variable1)))=1,Partner),$(Variable1)), aggr(if(rank(aggr(sum({<Year={$(=Max(Year)-1)}>} Sales),$(Variable1)))=1,Partner),$(Variable1))), 

Client), Client)

YELLOW:

aggr(

if( count(distinct Partner)>2

or

count(distinct Partner)=2

AND

only(aggr(if(rank(aggr(sum({<Year= {$(=Max(Year)-2)}>} Sales),Client,Partner))=1,Partner),Client,Partner))

<>

only(aggr(if(rank(aggr(sum({<Year= {$(=Max(Year)-1)}>} Sales),Client,Partner))=1,Partner),Client,Partner))

AND

only(aggr(if(rank(aggr(sum({<Year= {$(=Max(Year)-2)}>} Sales),Client,Partner))=1,Partner),Client,Partner))

<>

if(sum({<Year={$(=Max(Year))}>}Sales)>0, aggr(if(rank(

aggr(sum({<Year={$(=Max(Year))}>} Sales),$(Variable1))

)=1,Partner),$(Variable1)), aggr(if(rank(

aggr(sum({<Year={$(=Max(Year)-1)}>} Sales),$(Variable1))

)=1,Partner),$(Variable1)))

AND

only(aggr(if(rank(aggr(sum({<Year= {$(=Max(Year)-1)}>} Sales),Client,Partner))=1,Partner),Client,Partner))

<>

if(sum({<Year={$(=Max(Year))}>}Sales)>0, aggr(if(rank(aggr(sum({<Year={$(=Max(Year))}>} Sales),$(Variable1)))=1,Partner),$(Variable1)), aggr(if(rank(aggr(sum({<Year={$(=Max(Year)-1)}>} Sales),$(Variable1)))=1,Partner),$(Variable1)))

or

count(distinct Partner)=2

AND

count(distinct Year)=1, 

Client), Client)

RED:

aggr(if(count(Year)=0,  Client), Client)

[#2]

I've tried to use the calculated dimensions for the Green, Yellow and Red list boxes as a dimension of the pie chart and then add sum(Opportunity) as the expression but I can't get it to work so I went down the no dimension/3 expressions route which gave me results that are very close to what I should get.

However, in the Green expression, QlikView is only calculating the first sum() but not the following ones...

[#3]

so why the

K.N. SJVNTY DSWJTKOHCDR EPS / Zbanlfl

has not Top Partner 2011 and Top Partner 2010 value?:)

belive me this expression is wrong

change it and i'm sure we get the final answer:)

[#4]

ok let's start for the beginning

explain what is this expression for so i can rebuild it my way

only(aggr(if(rank(aggr(sum({<Year= {$(=Max(Year)-2)}>} Sales),Client,Partner))=1,Partner),Client,Partner))

[#5]

This is to call out the ID of the Partner that sold the most in Max(Year)-2 to each specific Client. If the Client hasn't purchased anything in Max(Year)-2 then it should show nothing. See results in straight table.

[#6]

and what is if(rank used for?

[#7]

If the Partner ranks nr 1 based on the given aggregation, then I want to see who that Partner is.

[#8]

Sorry, missed this question. K.N. SJVNTY DSWJTKOHCDR EPS / Zbanlfl doesn't have anything in 2011 and 2010 because they only purchased something in 2012 - the expression is correct.

[#9]

could you paste an island to the script

island:

LOAD * INLINE [

    colour

    Red

    Green,

    Yellow

];

and re-post the qvw file?

[#10]

Not sure if I understand what you want to do but here it is.

[#11 · Accepted solution]

I've finally managed to solve this!

The correct expressions are as follows:

Green:

sum(

{< Client= {"=sum(distinctPartner)=1"}> }

Opportunity)

+

//All 3

sum(

{< Client= {"=sum(distinctPartner)=2 and

sum(distinctYear)>1 and 

only(aggr(if(rank(aggr(sum({<Year= {$(=Max(Year)-1)}>} Sales),Client,Partner))=1,Partner),Client,Partner))=

only(aggr(if(rank(aggr(sum({<Year= {$(=Max(Year)-2)}>} Sales),Client,Partner))=1,Partner),Client,Partner)) and

only(aggr(if(rank(aggr(sum({<Year= {$(=Max(Year)-1)}>} Sales),Client,Partner))=1,Partner),Client,Partner))=

if(sum({<Year={$(=Max(Year))}>}Sales)>0, aggr(if(rank(

aggr(sum({<Year={$(=Max(Year))}>} Sales),$(Variable1))

)=1,Partner),$(Variable1)), aggr(if(rank(

aggr(sum({<Year={$(=Max(Year)-1)}>} Sales),$(Variable1))

)=1,Partner),$(Variable1)))"}> }

Opportunity)

+

//2011=MostRecent and 2011<>2010

sum(

{< Client= {"=sum(distinctPartner)=2 and

sum(distinctYear)>1 and 

only(aggr(if(rank(aggr(sum({<Year= {$(=Max(Year)-1)}>} Sales),Client,Partner))=1,Partner),Client,Partner))=

if(sum({<Year={$(=Max(Year))}>}Sales)>0, aggr(if(rank(

aggr(sum({<Year={$(=Max(Year))}>} Sales),$(Variable1))

)=1,Partner),$(Variable1)), aggr(if(rank(

aggr(sum({<Year={$(=Max(Year)-1)}>} Sales),$(Variable1))

)=1,Partner),$(Variable1))) and

only(aggr(if(rank(aggr(sum({<Year= {$(=Max(Year)-1)}>} Sales),Client,Partner))=1,Partner),Client,Partner))<>

only(aggr(if(rank(aggr(sum({<Year= {$(=Max(Year)-2)}>} Sales),Client,Partner))=1,Partner),Client,Partner)) "}> }

Opportunity)

+

//2011=2010 and 2011<>MostRecent

sum(

{< Client= {"=sum(distinctPartner)=2 and

sum(distinctYear)>1 and 

only(aggr(if(rank(aggr(sum({<Year= {$(=Max(Year)-1)}>} Sales),Client,Partner))=1,Partner),Client,Partner))=

only(aggr(if(rank(aggr(sum({<Year= {$(=Max(Year)-2)}>} Sales),Client,Partner))=1,Partner),Client,Partner)) and

only(aggr(if(rank(aggr(sum({<Year= {$(=Max(Year)-1)}>} Sales),Client,Partner))=1,Partner),Client,Partner))<>

if(sum({<Year={$(=Max(Year))}>}Sales)>0, aggr(if(rank(

aggr(sum({<Year={$(=Max(Year))}>} Sales),$(Variable1))

)=1,Partner),$(Variable1)), aggr(if(rank(

aggr(sum({<Year={$(=Max(Year)-1)}>} Sales),$(Variable1))

)=1,Partner),$(Variable1))) "}> }

Opportunity)

+

//2010=MostRecent and 2010<>2011

sum(

{< Client= {"=sum(distinctPartner)=2 and

sum(distinctYear)>1 and 

only(aggr(if(rank(aggr(sum({<Year= {$(=Max(Year)-2)}>} Sales),Client,Partner))=1,Partner),Client,Partner))=

if(sum({<Year={$(=Max(Year))}>}Sales)>0, aggr(if(rank(

aggr(sum({<Year={$(=Max(Year))}>} Sales),$(Variable1))

)=1,Partner),$(Variable1)), aggr(if(rank(

aggr(sum({<Year={$(=Max(Year)-1)}>} Sales),$(Variable1))

)=1,Partner),$(Variable1))) and

only(aggr(if(rank(aggr(sum({<Year= {$(=Max(Year)-2)}>} Sales),Client,Partner))=1,Partner),Client,Partner))<>

only(aggr(if(rank(aggr(sum({<Year= {$(=Max(Year)-1)}>} Sales),Client,Partner))=1,Partner),Client,Partner)) "}> }

Opportunity)

Red:

sum(

{< Client= {"=sum(distinctPartner)=0"}> } Opportunity)

Yellow:

sum(Opportunity) - Red - Green

Went the easier way with the Yellow. Have also created a distinctPartner field to avoid all those count(distinct).