stacked bar with aggr in dimension

[#1]

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,

[Jason_Michaelides]

Divide your current expression by:

COUNT(TOTAL DISTINCT CLASS)

[Jason_Michaelides · Accepted solution]

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

[#4]

Hey Jason,

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

i'm checking out your file now.

[#5]

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

Big thanks and wish you a great sunday ahead mate !

[Jason_Michaelides]

No probs.

[#7]

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?

Thanks for your help.

[Jason_Michaelides]

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

[Jason_Michaelides]

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

[#10]

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?

[Jason_Michaelides]

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