Dimension Limit

[karin_nevo]

HI,

I have stacked Bar chart with 2 dimensions: Year and Type.

I would like to display the largest 5 Types for each year (stacked bar).

I tried to use Dimensions Limits on Type field with 'Global Group Mode',but I get incorrect results.

I also tried to use Rank function:

if(aggr(rank(count(distinct EMP_ID)),Type)<=5,Type)

But I was unable to change it to include Aggr for year and Type. With the current expression, the Type is evaluated for the entire chart and not per year.

thanks,

Karin

[Anonymous]

please post some sample data...

[karin_nevo]

HI,

QV file is attached.

Notice that if I select specific year - the chart on the left displays the correct values, but when a year is not selected the values are changed/

thanks for your help

[Anonymous]

could you upload also: DataScramble.xlsx

or put the data inline?!?

[antoniotiman]

Hi Karin,

See Attachment.

Dimension Limits is per Chart.

See Option 2.

Regards,

Antonio

[Kushal_Chawda]

is this what you are looking for?

[karin_nevo]

Thanks Antonio.

The solution you sent is working.

I also need your help tryin g to sort the values within each year in decending order.

under Type sort I tried the expression: count( distinct EMPNUM) but it doesn't work.

I also tried to copy the Expression:

=if(aggr(rank(count(distinct EMPNUM)),Year,Type)<=5,count(distinct EMPNUM))

but it doesn't work either.

[karin_nevo]

Thanks, but the screenshot you sent doesn't show the correct values for each year.

[karin_nevo]

I am also trying to ignre Year selection using Set analysis and for some reason it doesn't work with this solution.

[karin_nevo]

Attached QVW with the data inline