Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Pie chart with no dimension

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)

19 Replies
Not applicable
Author

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

Not applicable
Author

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:)

Not applicable
Author

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))

Not applicable
Author

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.

Not applicable
Author

and what is if(rank used for?

Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

could you paste an island to the script

island:

LOAD * INLINE [

    colour

    Red

    Green,

    Yellow

];

and re-post the qvw file?

Not applicable
Author

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

Not applicable
Author

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