topic Re: Class aggregation (histogram) - force to start from 1 and not zero in QlikView

Class aggregation (histogram) - force to start from 1 and not zero

Fri, 25 Oct 2013 09:25:55 GMT

Hi,

I am using the following function to make a histogram :aggr(class(count(distinct Month),3),[All Codes])

When my class width is 1, my categories start from 1 (i.e. 1<=x<2), but when I set my class width as 3 as in the formula above, it unhelpfully starts from zero (.e. 0<=x<3). How can I make it so that my first category is 1<=x<4?

Many thanks,

Isabel

Re: Class aggregation (histogram) - force to start from 1 and not zero

swuehl — Fri, 25 Oct 2013 09:28:42 GMT

Have you tried using the offset parameter to class() function?

aggr(class(count(distinct Month),3, 'x', 1 ),[All Codes])

Re: Class aggregation (histogram) - force to start from 1 and not zero

Fri, 25 Oct 2013 09:32:41 GMT

That works a dream - thank you

Re: Class aggregation (histogram) - force to start from 1 and not zero

Fri, 25 Oct 2013 10:06:02 GMT

Hi,

Is it possible to also cap the expression, so I have 7+ as one category? Dimension limits want to work of the value in the category, rather that the category its self!

Many thank,

Isabel

Re: Class aggregation (histogram) - force to start from 1 and not zero

swuehl — Fri, 25 Oct 2013 10:09:40 GMT

Maybe like this:

aggr(if(count(distinct Month)<7,class(count(distinct Month),3, 'x', 1 ), dual('7+',7) ) ,[All Codes])

Re: Class aggregation (histogram) - force to start from 1 and not zero

Fri, 25 Oct 2013 10:11:50 GMT

And another question that your experience may help with:

Do you have any tips on how to make this into a % graph, so like a mecca but with the same column widths? I would normally divide by the sum of the category total but I can't see how I'd code in the category total since it is not a defined field!

Thanks for your help, I really appreciate it,

Isabel

Re: Class aggregation (histogram) - force to start from 1 and not zero

Fri, 25 Oct 2013 10:13:24 GMT

This worked perfectly too - I'm so pleased. Thanks for your quick responses!

Isabel

Re: Class aggregation (histogram) - force to start from 1 and not zero

swuehl — Fri, 25 Oct 2013 10:25:06 GMT

Not sure if I understood your % graph request correctly.

What is your current expression in the chart? Something like

=count(distinct [All Codes])

Then try

=count(distinct [All Codes]) / count(total distinct [All codes])

If you are using a second dimension, Category, then you can add this to the total field list:

=count(distinct [All Codes]) / count(total<Category> distinct [All codes])

And format this expression as percentage on number tab.