topic Re: Dimension Limit in QlikView

Dimension Limit

karin_nevo — Mon, 01 May 2017 07:45:04 GMT

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

Re: Dimension Limit

Anonymous — Mon, 01 May 2017 07:50:05 GMT

please post some sample data...

Re: Dimension Limit

karin_nevo — Mon, 01 May 2017 09:12:15 GMT

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

Re: Dimension Limit

Anonymous — Mon, 01 May 2017 09:36:16 GMT

could you upload also: DataScramble.xlsx

or put the data inline?!?

Re: Dimension Limit

antoniotiman — Mon, 01 May 2017 09:52:24 GMT

Hi Karin,

See Attachment.

Dimension Limits is per Chart.

See Option 2.

Regards,

Antonio

Re: Dimension Limit

Kushal_Chawda — Mon, 01 May 2017 10:49:47 GMT

is this what you are looking for?

Re: Dimension Limit

karin_nevo — Wed, 03 May 2017 05:43:39 GMT

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.

Re: Dimension Limit

karin_nevo — Wed, 03 May 2017 05:44:51 GMT

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

Re: Dimension Limit

karin_nevo — Wed, 03 May 2017 05:54:23 GMT

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

Re: Dimension Limit

karin_nevo — Wed, 03 May 2017 05:58:14 GMT

Attached QVW with the data inline

Re: Dimension Limit

antoniotiman — Wed, 03 May 2017 07:10:26 GMT

Hi Karin,

sort is per chart.

TOTAL AN, TOTAL QQ, TOTAL GG, ....

Regards,

Antonio

Re: Dimension Limit

karin_nevo — Wed, 03 May 2017 09:01:14 GMT

I didn't understand your reply.

I need for each year to sort by type (highest to lowest).

Also, do you know why the set analysis to ignore year doesn't work here?

Re: Dimension Limit

antoniotiman — Wed, 03 May 2017 09:08:29 GMT

There's no way.

Sort is evaluate per Chart (Sum of all Year/Type selected)

Re: Dimension Limit

karin_nevo — Wed, 03 May 2017 09:18:24 GMT

I understand, thanks...

I have another question, instead of counting employees, I am using:

count(distinct EMPNUM)/count(distinct Total <Year>EMPNUM))

to show the precentage of each type per year.

is there a way to show 'Others' as a sum of all types that are ranked higher than 5?

I would like all columns to end at the same line (similar to the top chart in the QVW file)

Re: Dimension Limit

antoniotiman — Wed, 03 May 2017 10:22:32 GMT

See Attachment Sheet 'Foglio1'

Regards,

Antonio

Re: Dimension Limit

karin_nevo — Wed, 03 May 2017 11:11:19 GMT

This solution doesn't display the correct data.

For example - in 2016: if no year is selected HH (6.58%) is missed in the list.

It has the same problem that I used in my original file

Re: Dimension Limit

antoniotiman — Wed, 03 May 2017 12:02:30 GMT

See Attachment

Re: Dimension Limit

karin_nevo — Wed, 03 May 2017 12:47:57 GMT

Great, thank you so much for your help!