How to display the current month's bar in the bar chart first?

[Anonymous]

Hi everyone,

This is my question, Assume that I have sales data from the month Jan 2013 to Jan 2014 in excel document. Then I am creating a barchart in qlikvew, Now I want to display the the current month's bar first, if this month is feb then Feb's bar should be displayed first. Is this possible to do in qlikview?

Thanks,

Varun Krishna. P

[#2 · Accepted solution]

OK. In any case you'll need to order months. Balancing between text and numeric display of the month is linked to Month() function. The text display is usually a shortened version of the month name, like Jan, Feb etc. Normally month names list is displayed as a parameter at the beginning of the script. It can eventually be changed to match with the complete month names you have in your Excel files (but it could cause other problems). Otherwise, it can be done in your graph with Match() function.

2nd month in your Excel file is Feburary, I guess it's just a typo to be corrected in Excel.

In your graph, first dimension is Month column, second is Year.

1) Month: turn the dimension into a calculated one:

     =if(Match(Month,'January','February','March','April','May','June','July','August','September','October','November'

          ,'December')>=Num(Month(Today())), Month)

Name that dimension Month, tick the box to not show nulls, you'll get the month names of current month and after.

2) To order months: I see your Excel file has a Date field in 1st column, if it's loaded it Qlikview it will ease matters, you can sort months by this expression ascending:

     =Avg(Num(Month(Date)))

If Date field is not in Qlikview, you can use Match() again, sorting with this expression ascending:

     =Avg(Match(Month,'January','February','March','April','May','June','July','August','September','October','November'

          ,'December'))

3) Add Year as a second dimension, it should be naturally sorted in chronological order.

This should lead to the graph you want.

Hope it helps.

[Anonymous]

Pierre de Calan,

It is not working, ok, But I want to display the individual sales data on the bar for each month i.e. separate bars for January 2010, January 2011 and so on..., but my bar is now displaying the sum of sales for the month January,i.e it sums up the sales for the month January 2010+ January 2011+.....+January 2014 where I have gone wrong ?

Thanks,

Varun Krishna. P

[#4]

This should change when you add Year as a second dimension.

At worst, tomorrow I'll be back on job, will work on your qvw file.

[Anonymous]

Hi Pierre de Calan,

Yes it is working, the mistake I did was "I did not add that = sign for if and Avg so it did not work" ,the next mistake which I did was in the style page(Chart Properties>Style>Subtype) I had chosen "Stacked" instead of "Grouped". So it stacked all the January's together.  Here is the finished chart.

Thanks,

Varun Krishna. P