topic Re: Un-Group the Dimensions in Chart in QlikView

Un-Group the Dimensions in Chart

sureshbaabu — Fri, 14 Sep 2012 05:06:24 GMT

Hello,

I have a requirement to represent a data by month using charts and I wanted to display the first character of the Month names as below

Qlikview groups the common fields together. So for Months, All ‘J’,’A’ and ‘M’ are getting grouped

could you please assist me with the process to ungroup the dimension ?

Thanks

Re: Un-Group the Dimensions in Chart

Jason_Michaelides — Fri, 14 Sep 2012 07:42:42 GMT

Try this as your dimension:

=Date(Month,'M')

This should only affect the formatting and not the underlying numerical value.

Hope this helps,

Jason

Re: Un-Group the Dimensions in Chart

sureshbaabu — Fri, 14 Sep 2012 11:41:42 GMT

Hello Jason,

Thanks for your Response!!!

Could you pease explain in detail if you dont mind. I'm not getting it clearly.

For Eg:

My cloumn name is 'MONTH'

My data for column 'MONTH' is 'JAN,FEB, MAR,.....,DEC'

MY dimension is MONTH on the Chart.

Could you please help me with the Expression for the above example?

Thanks

Re: Un-Group the Dimensions in Chart

Jason_Michaelides — Fri, 14 Sep 2012 11:43:38 GMT

Remove MONTH as the dimension. Click the Add Calculated Dimension button and type:

=Date(MONTH,'M')

In the Label box on the dimension tab type 'Month'

See if that works,

Jason

Re: Un-Group the Dimensions in Chart

sureshbaabu — Fri, 14 Sep 2012 11:50:47 GMT

Hello,

No, It does not work. It shows a null coulmn

Thanks

Re: Un-Group the Dimensions in Chart

Jason_Michaelides — Fri, 14 Sep 2012 11:53:45 GMT

How did you get your MONTH field? Is it text or a number (date) formatted as 'MMM' ?

If it is text then you will need to add this to the script:

LOAD

Otherfields,

Date(Date#(MONTH,'MMM'),'M') AS MONTH

FROM.....;

If you post your app I'll show you.

Re: Un-Group the Dimensions in Chart

sureshbaabu — Fri, 14 Sep 2012 12:14:56 GMT

My data comes as a text. The above expression will give us only the numbers from 1 to 12 right?

LOAD

Month,
Date(Date#(Month,'MMM'),'M') AS MONTH_NEW,
Value
FROM
[test.xls]
(biff, embedded labels, table is

Sheet1$);

my dimension is 'MONTH_NEW' in the chart.

Thanks

Re: Un-Group the Dimensions in Chart

Jason_Michaelides — Fri, 14 Sep 2012 12:20:39 GMT

Yes - sorry. Try:

Dual(Left(MONTH,1),Date#(MONTH,'MMM')) AS MONTH_NEW

The Date#() coverts the text into a date (numeric) field. Dual allows you to have similar looking values but with different underlying numeric values.

Now, in your chart if you use MONTH_NEW you should be good to go.

Jason

Re: Un-Group the Dimensions in Chart

sureshbaabu — Fri, 14 Sep 2012 13:27:33 GMT

This is Awesome. It Worked.

Thank you taking your time in helping me.

I have learnt something new from new. Thanks a lot Jason

Re: Un-Group the Dimensions in Chart

Jason_Michaelides — Fri, 14 Sep 2012 13:28:29 GMT

No worries.