Show rolling 12 Months only for last 12 months.

[#1]

Hello,

I asked this question earlier and thought I solved it, but it is not working now. I need to sort the legend (brand) of my line chart by highest Units sold for the last selected date. E.g. if 2010-2012 were selected, then sort by 2012 unit sales. (In this case, however, it is a rolling 12 Months share. So if the years 2010-2014 were selected, then it would  calculate rolling 12 month for the range: October 2013 : September 2014. September 2014 being the current month.)

Under the sort tab, for calculated expressions, I have tried several different things:

aggr(RangeSum(Above(Sum({$<Year>} {<Date = {'$(=Max(Date))'} >}Units),0,12)), $(v_sortSelected),Date)

For my first attempt, I added {<Date = {'$(=Max(Date))'}>}  to try and limit my rolling 12 months only to the last 12 months. This resulted in limiting my result to only the last month. So I tried a few ways to capture the date range:

aggr(RangeSum(Above(Sum({$<Year>} {<Date = {">=$(=Max(Date) - 11)<=$(=Max(Date))"}>} Units),0,12)), $(v_sortSelected),Date)

(Sum({<Date = {">=$(Addmonths(Max(Date),-12))<=$(=Max(Date))"}>} Units),0,12)), $(v_sortSelected), Date)

(Sum({<Date = {">=$(Above(Max(Date),0,12))<=$(=Max(Date))"}>} Units),0,12)), $(v_sortSelected),Date)

Nothing worked. I assume it has something to do with my date format, which was uploaded as: Date(MakeDate(Year(Date#(YEAR,'YYYY')),Month(Date#(MONTH,'MMM'))), 'MMM YYYY') as Date

Are there any solutions to this?

[afurtado]

Hi.

if you have date in your dimensions, sometimes I use "if" to show the dates that I whant. Check box to don't show null values in dimensions.  I know that is not good about performance, but for small data.....

eg.    If(field_date >= var,field_date)

Best,

Alessandro Furtado

[#3]

Hi Alessandro, thanks for the response.

How would I write that into my expression? The field_date is a variable, where the user could potentional select anything from 1990 to current, or all of the above. If they selected 1995-2013, then I would need rolling 12 monts for Jan 2013 to Dec 2013, the last selected date. I guess I am not sure what variable I would make field_date >= to, although maybe I am not thinking about it right.

Also, are if statements in general bad for performance? My expression actually already include 6 if statements based on variable. For example, if vExample = 1, then the [equation posted above], if vExample = 2, then [second equation], etc... I did this so that with a button press you can change from annual to quarter to monthly, and rolling 12 month, etc., all on the same chart, rather than managing six seperate charts. Basically, all dimensions are variables and the expression contains 6 if statements to switch to different calculations depending on which button you press.

[stevedark]

Hi Abraham,

You may want to take a look at this example on using Set Analysis for creating periods:

QlikView App: Simple Year To Date Example (Set Analysis)

There is also a blog post I created about creating extra dimensions to allow for rolling periods, this can be a particularly useful technique:

http://www.quickintelligence.co.uk/qlikview-accumulate-values/

There is also an example QlikView app associated with that post.

Hope that helps,

Steve

[#5]

I wanted to respond to myself with an additional question / clarification. Many times I have run into a problem where I have problems with formulas that include dates, because of the format. Is there a function that you can reference the last selection, rather than max(selection), in the event that the data is not a number? Or can you reference 12 selections up or down, rather than Date - 12 or addmonths(), etc.?

For example, if a user selects 2010-2012, then I want to reference the last selected (2012) + 12 above that. I need a function that ignores whether it is a number or a string, or format entirely, and looks 12 spaces up in the data -- if that makes sense.

[#6]

Thanks Steve, I will read through it.

[stevedark]

The formats of dates can be a problem, particularly when inserting into Set Analysis.  You will see how this is handled in my example.

In short, if you format the value to match the format in the underlying data model you should be okay, eg.

Create a variable vMaxDate, with the following:

=Date(Max(MyDate), 'DD MMM YYYY')

And then use this in Set Analysis like this:

Sum({<MyDate={'$(vMaxDate)'}>}MyValue)

By moving the derivation of the date into a variable and formatting it you are both keeping your code clean and ensuring a format match.

Usually with a bit of fiddling and find tuning you can get to what you need.  Worst case scenario you can carry fields, such as month, both as Dual and Numeric versions.

Hope that helps,

Steve

[#8]

Hi Steve,

I tried creating some variables, but even when making the date format YYYY-MM-DD, AddMonths() does not seem to do anything. For example:

vMaxDate: =Date(Max(Date), 'YYYY-MM-DD')

vMaxDate12: =addmonths(Date(Max(Date), 'YYYY-MM-DD'),-12)

Then:

aggr( RangeSum( Above( Sum({$<Year>} {<Date = {">=$(vMaxDate12)<=$(MaxDate)"}>} Units),0,12)), $(vCompanyBrand), Date)

Shouldn't that give me what I need? Rolling 12 months only for the last 12 months selected?

The expression below this sentence works for rolling 12 months -- I am having trouble understanding why the one above does not limit it to the dates I need.

aggr( RangeSum( Above( Sum({$<Year>} Units),0,12)), $(vCompanyBrand), Date)

[stevedark]

Hi Abraham,

If those variable names are correct, and you have copied and pasted the expression, it looks like you have missed a prefix on the MaxDate variable.

You have <=$(MaxDate) where it should be <=$(vMaxDate)

Also, where you have the AddMonths function in the variable, I would put the Date function around the outside, as it is possible that AddMonths strips the formatting off again, so it would read:

=Date(AddMonths(Max(Date), -12), 'YYYY-MM-DD')

Hopefully one (or both) of those things will fix the issue you are having.

Steve

[#10]

Thanks. I made the corrections, but it still does not work. I am testing it out on a pivot table and the rows have "-". I am guessing the error is specific to the addmonths() function, because Max(Date) works. I cannot figure out how to go 12 months previous though.