19 Replies Latest reply: Jun 26, 2012 9:34 AM by Jason Michaelides

# stacked bar with aggr in dimension

Hi,

I have this in dimension for stack bar.

=AGGR(sum(NUMBER) ,CLASS ,MONTH)

then in expression i have,

COUNT(AGGR(sum(NUMBER) ,CLASS ,MONTH))

the result of dimension will be 0, 1, 2.

the expression is couting, how many of 0, how many of 1, and 2.

couting is fine, but when i turn it to percentage, it wont work.

i have tried relative, etc... i need to get the sum of the count, but with the aggr in dimension always showing, i can't get the total.

i have also tried to split the count, but there's no result

if( (AGGR(sum(NUMBER) ,CLASS ,MONTH))=0, count(AGGR(sum(NUMBER) ,CLASS ,MONTH)) )+

if( (AGGR(sum(NUMBER) ,CLASS ,MONTH))=1, count(AGGR(sum(NUMBER) ,CLASS ,MONTH)) )+

if( (AGGR(sum(NUMBER) ,CLASS ,MONTH))=2,count(AGGR(sum(NUMBER) ,CLASS ,MONTH)) )

any hints?

Thanks,

• ###### Re: stacked bar with aggr in dimension

Just thinking out loud really - need to test it, but could you divide by the same Aggr() without one of the dimensions in it? Or maybe use TOTAL in the denominator expression.

• ###### Re: stacked bar with aggr in dimension

yup.. i tried all those possible ways i could think of... and i'm still thinking ...

• ###### Re: stacked bar with aggr in dimension

Hi Jason,

here is it, instead of showing count figures. i am trying to make 100% stacked bar chart. normally it's easy. but with aggr in dimension, it becomes tricky.. for me...

• ###### Re: stacked bar with aggr in dimension

Will take a look this evening when I get back mate.

• ###### Re: stacked bar with aggr in dimension

Hi Nick,

Sorry - I only got to look at this this morning.  Your expression (and dynamic dimenasion) aggregate over MONTH and CLASS.  You have MONTH as a dimension, therefore your divider needs to be a TOTAL count of CLASS.  Try this:

COUNT(aGGR(sum(FP_NB_COUNTER) ,PC_CLASSI_LABEL,MONTH_YEAR))/COUNT(TOTAL DISTINCT PC_CLASSI_LABEL)

Seems to work.

Hope this helps,

Jason

• ###### Re: stacked bar with aggr in dimension

Does everything I type need moderator approval!?

• ###### Re: stacked bar with aggr in dimension

I guess not...

• ###### Re: stacked bar with aggr in dimension

COUNT(TOTAL DISTINCT PC_CLASSI_LABEL)

• ###### Re: stacked bar with aggr in dimension

COUNT(TOTAL DISTINCT CLASS)

• ###### Re: stacked bar with aggr in dimension

Woohoo! After about 6 attempts I got a post past the bloody moderator rules!  It seems your full field name for CLASS sets off alarms!

Fuller explanation coming once it gets approved...

• ###### Re: stacked bar with aggr in dimension

Hey Jason,

Thanks for getting back.. what moderator approval? i didn't get any?

i'm checking out your file now.

• ###### Re: stacked bar with aggr in dimension

geeeez, i did try that, but without distinct. stuck for so many days.

Big thanks and wish you a great sunday ahead mate !

• ###### Re: stacked bar with aggr in dimension

No probs.

• ###### Re: stacked bar with aggr in dimension

Hi Jason,

I was wondering, if there's any ways we could also include a cyclic into the dimension, for example - zone?

the total are not the same by zone, that's why the stacked bar is not 100% when we switch to zone.

i'd like to know if there's any way QV can still do it?

• ###### Re: stacked bar with aggr in dimension

That one was fun!  The normal TOTAL won't work as the Aggr() is screwing it up - probably didn't notice it before as the number was the same for every month.  This one seems to work thouhg...

(COUNT(Aggr(sum(FP_NB_COUNTER) ,PC_CLASSI_LABEL,\$(= GetCurrentField("New Group"))))-NullCount(DISTINCT PC_CLASSI_LABEL))/Aggr(NODISTINCT COUNT(DISTINCT PC_CLASSI_LABEL),\$(= GetCurrentField("New Group")))

Hope this helps,

Jason

• ###### Re: stacked bar with aggr in dimension

That one was fun!  The normal TOTAL won't work as the Aggr() is screwing it up - probably didn't notice it before as the number was the same for every month.  This one seems to work though...(again I have had to rename your CLASS field to get past the moderator rules)

(COUNT(Aggr(sum(FP_NB_COUNTER) ,CLASS,\$(= GetCurrentField("New Group"))))-NullCount(DISTINCT CLASS))/Aggr(NODISTINCT COUNT(DISTINCT CLASS),\$(= GetCurrentField("New Group")))

Hope this helps,

Jason

• ###### Re: stacked bar with aggr in dimension

Thanks Jaons it works !!!

Suddenly, it sparks another question.

Says i have a bar chart by month and in accumulation mode.

if i change the dimension from month to another says - zone.

would the expression make sense?

For those kind of chart, i always advise the users, if they are not looking at month dimension, make sure there's only one line in the table and select only one month year, but it's annoying because we can't compare to other data anymore.

In your experience, do you think in a monthly accumulated chart, would still work if the dimension is changed to others?

• ###### Re: stacked bar with aggr in dimension

Accumulation normally only makes sense when the dimension is something that incrementally increases.  So any date field is ideal - other types maybe not.  I wouldn't completely rule it out though.  To be honest I've not used accumulation much.

Jason