19 Replies Latest reply: Aug 2, 2012 6:55 PM by Linda Diodato RSS

    Pie chart with no dimension

    Linda Diodato

      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)