topic Bar chart help ! in QlikView

Bar chart help !

Mon, 14 Mar 2011 14:45:44 GMT

I need to create a bar chart for every 6 months sales. I have a date field. How can I create a dimension (extracting from date field) for every 6 months interval on X-axis ?

For ex: Jan-2008, Jul-2008,Jan-2009, Jul-2009 so on…

Many thanks

Bar chart help !

Oleg_Troyansky — Mon, 14 Mar 2011 14:55:22 GMT

I'd add another field into the Calendar table and calculate an extra field that can be used as a dimension.

I'm sure there is more elegant way of calculating it, but at the moment all i can think of id this:

date(if(month(Date) <7, YearStart(Date), YearStart(Date, 0, 7) ), 'MMM-YYYY') as SixMonthsBucket

You can use the same formula in the chart, but it might be too heavy for large data sets.

cheers,

Bar chart help !

Tue, 15 Mar 2011 10:08:53 GMT

Thanks Oleg..That's working fine.

One more small question. When i added that dimension to bar chart..iam getting 'SixMonthsBucket' values from Jan-2004 to Jan-2011. If i want to see only from Jan-2008 onwards...what will be the expression ?

I tried the below code in the expression but still getting same values from Jan-2004.

Code: If(SixMonthsBucket>'2008',SixMonthsBucket)

Many thanks

Bar chart help !

Tue, 15 Mar 2011 10:53:01 GMT

Hi,

Have you considered creating an independent Year-Month number using the autonumber function in your script.

In other words, create a line in your calendar script, such as:

autonumber(date(monthstart(Date), 'YYYYMM'), 99) as YMNum,

This orders all the dates in chronological order, due to the YYYYMM and then uses the autonumber to give each Year-Month value an independent ascending numerical value.

Then you can use set analysis against this number, such as:

sum({$<YMNum={$(=max(YMNum)), Year=, Month=, Day=}>} Sales) for the latest value and

sum({$<YMNum={$(=max(YMNum)-N)}, Year=, Month=, Day=>} Sales) for any year-month value N-months prior.

This technique allows you also to cross the Year divide line without the typical problems associated to making a selection on Year, e.g. if I use set analysis to go YMNum & YMNum-1, and my end user selects January of a Year, the 1st column will show Jan of the year and the 2nd column will show December of the previous year.

Hope it helps.

Bar chart help !

Oleg_Troyansky — Tue, 15 Mar 2011 18:55:48 GMT

You can do it in a couple of ways... I usually prefer to alter the Expression and not to build complicated Calculated Dimensions - those had proven to be quite heavy.

What I'd do in your case is add a Set Analysis condition in your Expression - something like Year={">=2008"}. This will rule out any numbers prior to 2008