topic Re: Relabelling Class Function Groups in QlikView

Relabelling Class Function Groups

mattphillip — Wed, 03 Dec 2014 14:38:04 GMT

Hoping someone can help. I've used the class function to group ages to produce a bar chart. However, the labels on the X axis are not very clear as to what they refer (e.g. 20 <= 21 < rather than 20 to 29.). The calculated dimension looks like this - =Class(Age,10,Age). I've seen where some have added text boxes to cover up the labels, but is there a way of actually customising the labels?

Any help would be much appreciated.

Matt

Re: Relabelling Class Function Groups

kiranmanoharrode — Wed, 03 Dec 2014 14:50:08 GMT

Dear Matt,

Use replace() function for replacing '<= x <' with 'to'.

=Replace(Class(Age,10),'<= x <', 'to')

Regards,

Kiran

Re: Relabelling Class Function Groups

JonnyPoole — Wed, 03 Dec 2014 14:50:36 GMT

You can wrap the result in a replace() function to get what you want . Replace '<=x <' with something better like to

replace( Class(Age,10,Age) , '<= x <' , 'to')

Re: Relabelling Class Function Groups

morganaaron — Wed, 03 Dec 2014 14:52:30 GMT

Hi Matt,

Try using the Replace function, in the format:

Replace(Class(....), '<= x <', ' to ') - this should replace the standard text a class bin width uses with your "to".

Re: Relabelling Class Function Groups

lironbaram — Wed, 03 Dec 2014 14:56:00 GMT

hi have a look at the attach example

simple replace function will do the trick

Re: Relabelling Class Function Groups

mattphillip — Wed, 03 Dec 2014 15:00:19 GMT

Thanks Kiran (and everyone else too). Works a treat! The only thing I've noticed though is that age groupings overlap e.g. 20 to 30 (which doesn't feature anyone aged 30), 30 to 40 (which does feature those aged 30 but no one aged 40). Is there anyway to tweak this so there is no overlap e.g 20 to 29, 30 to 39?

Any help would be much appreciated.

Matt

Re: Relabelling Class Function Groups

JonnyPoole — Wed, 03 Dec 2014 15:11:27 GMT

I played around a bit more. Here is a full customization that seems to work. I'm doing a '-1' at the end of the 2nd value to reduce it by one notch. Does it help ?

left( Class( Sales, 1000), findoneof( Class( Sales, 1000),' ',1))

& ' to ' &

( Num(mid( Class( Sales, 1000), findoneof( Class( Sales, 1000),' ',4)))-1)

Re: Relabelling Class Function Groups

mattphillip — Wed, 03 Dec 2014 15:18:21 GMT

Perfect Jonathan! Works a treat Many thanks everyone!

Matt

Re: Relabelling Class Function Groups

JonnyPoole — Wed, 03 Dec 2014 15:20:27 GMT

Yes ! sorry i posted the example from my own data you are correct

everything else is right. the first space denotes the end of the first number, the 4th space denotes the start of the 2nd number...thats the key for the findoneof() function.

number <= x < 2ndnumber

Re: Relabelling Class Function Groups

mattphillip — Wed, 03 Dec 2014 15:26:23 GMT

Ah, one slight fly in the ointment. Whilst it has sorted the groupings, it now features an empty first grouping of <blank> to <blank> with no values. I'm assuming its trying to group 0-9. Is there an easy way of hiding this empty grouping?

Re: Relabelling Class Function Groups

JonnyPoole — Wed, 03 Dec 2014 15:28:58 GMT

can you post a screenshot before and after please ?

Re: Re: Relabelling Class Function Groups

mattphillip — Wed, 03 Dec 2014 15:38:37 GMT

See attached.

Re: Relabelling Class Function Groups

JonnyPoole — Wed, 03 Dec 2014 16:14:58 GMT

i suppose you could wrap it in a If( not Isnull() ) condition to only render if its a legitimate grouping. I'm not sure where that group comes from though if the regular class() didn't yield such a bucket. If i were to guess, there may be NULLs in the data model that show up with this methodology (but not with class() ) and we now have to manually suppress them.

if( not isnull(left( Class( Age, 10), findoneof( Class( Age, 10),' ',1)) ),

left( Class(Age, 10), findoneof( Class( Age, 10),' ',1))

& ' to ' &

( Num(mid( Class( Age, 10), findoneof( Class( Age, 10),' ',4)))-1)

)

You may need to 'suppress nulls' on the dimension tab properties too. not sure becuase i don't get this result with my test data to test against.

Re: Relabelling Class Function Groups

mattphillip — Thu, 04 Dec 2014 08:32:50 GMT

Thanks Jonathan. It works! Much appreciated for all the support.

Matt

Matt Phillip

Academic Administration Officer (Strategic Planning)

Planning Office

Abertay University

The University of Abertay Dundee is a charity registered in Scotland, No: SC016040