Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Not applicable
‎2012-07-31 04:28 AM

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)

Sample file.qvw
2,341 Views
0 Likes
1 Solution

Accepted Solutions
Not applicable
‎2012-08-02 06:55 PM
Author

In response to

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

View solution in original post

1,213 Views
0 Likes
19 Replies
Not applicable
‎2012-07-31 04:31 AM
Author

post a sample file

it'll be easier

873 Views
0 Likes
Not applicable
‎2012-07-31 04:38 AM
Author

In response to

It's kind of complicated - I'll see what I can do.

873 Views
0 Likes
Not applicable
‎2012-07-31 05:36 AM
Author

In response to

Ok now with sample file attached above.

873 Views
0 Likes
Not applicable
‎2012-07-31 11:52 AM
Author

In response to

Hi,

I think I'm almost there but there is one expression that is not working - see attached sample file.

In the pie chart, it looks like only the first part of the Green expression is being calculated - the total should be $218,918 however, for some reason, Client XX KMOUHGDSAUYE HKNTJKMU HLJMOTP / Mvchndz is being calculated in the Yellow slice.

I'm sure I'm missing something silly but I see double right now so can somebody help please?

Thanks

Sample file 2.qvw
873 Views
0 Likes
Not applicable
‎2012-08-01 03:42 AM
Author

In response to

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

this part of expression looks wrong (am i right?)

there can't be if after aggr

873 Views
0 Likes
Not applicable
‎2012-08-01 03:45 AM
Author

In response to

i mean it can be but there have to be something like this:

aggr (if(condiotion=qwe, sum(dimension)....

what i can see in your example is

aggr (if(condition=qwe, dimension1, dimension2

873 Views
0 Likes
Not applicable
‎2012-08-01 03:46 AM
Author

In response to

if we do the expression right than we can paste it as a dimension to the pie chart

it have to work:)

873 Views
0 Likes
Not applicable
‎2012-08-01 03:46 AM
Author

In response to

It works everywhere else...

See Top Partner 2011 and Top Partner 2010 columns in the straight table.

873 Views
0 Likes
Not applicable
‎2012-08-01 03:48 AM
Author

In response to

expression is not like dimension:)

873 Views
0 Likes