Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

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.

1 Solution

Accepted Solutions
Employee
Employee

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

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 ?

5 Replies
Employee
Employee

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

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 ?

Not applicable

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

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))

Not applicable

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

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

Employee
Employee

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

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'.

Not applicable

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

Thank you for your help that has worked perfectly.

Community Browser