Scroll Bar Selection, Show The Last Four Months of Max Date

[#1]

Hello all,

Have been struggling on this for a while. I have a scroll-bar at the top of my Qlikview Document, where I will select a year range, so like 15June2013 - 15June2014 (field name: SalesDate)

So I want a graph, where given this top selection, i get the sum of all sales for all of march 2014, and then all of april 2014, then may 2014, then june 2014 up to my current selection.

The thing that I am struggling with is that

=month(max(SalesDate))-1&'-'&Year(max(SalesDate)) works relatively well until i get to january, which gives back 0 as the month.

There must be a simpler formula to enable you to calculate the previous 3 months sales to plot in a graph.

[JonnyPoole · Accepted solution]

I created 2 variables (max month and min month. min month is max month - 3 months).

vMaxMonth=MonthEnd( max(Date))

vMinMonth=MonthStart( AddMonths( max(Date),-3))

Then i used this set analysis in the chart expression to only filter in dates that cover todays month and the last 4 months

sum( {$< Date={'>=$(vMinMonth)<=$(vMaxMonth)'}>}  Sales)

does it help ?

[#3]

The key is to use addmonths function to get the previous month instead of subtracting 1 from the Month() function.

Change your formula from month(max(SalesDate))-1&'-'&Year(max(SalesDate)) to Month(Addmonths(Max(SalesDate),-1))&'-'&Year(Addmonths(Max(SalesDate),-1))

[#4]

Hi Jonathan,

Thank you for your answer. It has saved me a considerable amount of time and is very clever way of doing things!

If I may, in addition, ask a question of the back of this:

     If there are a lot of different locations, so like London, New York, Paris etc how do you incorporate the above      solution so that the graph shows the last four months progress grouped by each location. Currently it is showing October 2013: London value, Paris value, New York value etc, however I would like this to change to the following:

London: Current Month Value, n-1 Month Value, n-2 Month Value, n-3 Month Value, n-4 Month Value.

I have tried changing the dimension to Location but this just produces a blank graph.

Thank you in advance,

James

[JonnyPoole]

I added Regions as a 2nd dimension.  By default the 2nd dimension will create 'clusters' of bars or stacks within a single bar for each unique value of the 1st dimension.  So in this case you get a bar or stack for each region and you get a cluster of these for each month in the chart.  To toggle between individual bars or stacks , go to the style tab of the chart properties and pick 'grouped' or 'stacked'.

[#6]

Thank you for your help that has worked perfectly.