Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

morganaaron
Valued Contributor

How to get a chart to show a chosen week order in the axes?

Hi there,

I'm currently creating a chart that shows the number of Items sold for two date ranges:

"Current" which shows the items sold for the selected week and the 51 weeks prior to that

"Last Year" which shows the items sold for the year long period prior to what's being shown above

There are date fields for fiscal week and which are formatted as 201501 through to 201552 for 2015 for example, and the periods are 201501 to 201512 for each year (so 2014 would be 201401 - 201412). There are also fields that just pick up the period and week number, i.e. 1-12 for period and 1-52 for week number.

The chart works in that it shows the given weeks value and the previous 51 weeks, and the "last year" variable shows the year prior to that, however I want these to be overlaid on each other. When I put the dimension as fiscal week, this separates them into a 2 year span, showing one after the other. When I put the dimension as week number, it correctly overlays the two charts, as both run a full "week number" year (i.e. they all have values from 1-52), however the graph axes defaults to showing 1-52, and what I want it to do is show from the selected week as the final value and the 51 previous weeks behind it, i.e. if they select week 30 I want the chart to start at 29 and end at 30 (i.e. the year transition would read 51, 52, 01, 02).

I thought it would work if I set the dimension sort order to be calculated based on the fiscal week, because the selected week would have the highest given fiscal week value, e.g. if I've selected 2014 and week 30, 201430 would be the highest value in the chart, but I've tried a few expressions based on this field and it seems to jumble the week number values without any real order.

Any help would be appreciated! Many thanks.

Tags (3)
1 Solution

Accepted Solutions
morganaaron
Valued Contributor

Re: How to get a chart to show a chosen week order in the axes?

Hi all,

Thanks for your suggestions. I've managed to get it to work by implementing a calculated dimension, if it's within the first calculated 52 week period, it simply shows the fiscal week, but for the previous year to then, it adds a set value on to the fiscal week, treating it as a number, which brings the "dimension" into line.

Thanks for your help!

Aaron

View solution in original post

8 Replies
Not applicable

Re: How to get a chart to show a chosen week order in the axes?

Dear Aaron,

If i have understood your problem correctly the solution may be as simple as sorting your weeks by load order. You can then alternate between original and reverse to get the view you are after.

Hope that helps.

Regards

R

morganaaron
Valued Contributor

Re: How to get a chart to show a chosen week order in the axes?

Hi Rahul,

Thanks for your reply. I tried load order, and this pushes week 52 to the start, and then runs through 01,02..but it doesn't seem to work for what I'm wanting.

Aaron

qlikoqlik
Contributor II

Re: How to get a chart to show a chosen week order in the axes?

Hi Aaron

You custom sort using an expression =match(Country,'India','China','Thailand')

you should enter the above function as an  expression in the sort tab of properties

Regards

Padma

morganaaron
Valued Contributor

Re: How to get a chart to show a chosen week order in the axes?


Hi Padma,

Thanks for the suggestion! I tried using the match function but again without much success, it seems to randomise all the fiscal weeks aside from the weeks within the period I've selected (so if weeks 30-34 sit within a period, those would be in the correct order, but the rest then seem randomly distributed).

Aaron

morganaaron
Valued Contributor

Re: How to get a chart to show a chosen week order in the axes?

Hi all,

Thanks for your suggestions. I've managed to get it to work by implementing a calculated dimension, if it's within the first calculated 52 week period, it simply shows the fiscal week, but for the previous year to then, it adds a set value on to the fiscal week, treating it as a number, which brings the "dimension" into line.

Thanks for your help!

Aaron

View solution in original post

Not applicable

Re: How to get a chart to show a chosen week order in the axes?

Hi Aaron,   Can you give me more information regarding how you were able to fix this?   I am having the same issue and cannot resolve it.

morganaaron
Valued Contributor

Re: How to get a chart to show a chosen week order in the axes?

Hi Joe,

This was quite a while ago, and I think the solution I ended up implementing was pretty poor practice even though it worked!

What I now do with this scenario is:

Create a field in the load that strips out the week number (in this case for each fiscal week that is 1-52) - in most cases because my data is uniform I use Right(FiscalWeek, 2) in the date load to get this, as my fiscal week is always formatted as 6 digits.

I then use this field as my dimension and use two separate expressions, one for each time period, to plot the data. What I sometimes do is turn off the axis labels for the weeks and add a text on axis only expression of Max(FiscalWeek) to return the valid weeks for the current term, if that makes sense!

Highlighted
Not applicable

Re: How to get a chart to show a chosen week order in the axes?

Thanks Aaron, I was able to get this to work.