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?

[stevedark · Accepted solution]

Hi Abraham,

I'm not sure what the {$<Year>} is doing in your code, this would appear to have no bearing on things.

Breaking things out into smaller chunks can help with validation.  For instance, do a column where the date is greater than the 12 month back date and another column which is less than the greatest date.  When you check the parts out you can then start to put them together.

Check the values in the variables (text boxes are good for this) to ensure they are what you expect.

Using the aggregation in the data model (eg. creating 12 months associated with each month) as advocated in my blog post makes things a lot simpler.  Rather than doing the RangeSum , Aggr and Above you simply change the dimension and everything else works itself out.

Steve

[#3]

Thanks for the suggestion Steve, using text boxes to test actually helped me solve it. All I did was add an equals sign at the after this: sum({<Date = {">=$( 

sum({<Date = {">=$(=Date(AddMonths(Max(Date),-11), 'MMM YYYY'))"}>} Units)       

I got the variables to work using the text box test. In this case they worked when I deleted the preceeding equals sign: (I don't understand why.)

vMaxDate - Before:  =Date(Max(Date), 'MMM YYYY')         

vMaxDate - After:     Date(Max(Date), 'MMM YYYY')  

vMaxDate12 - Before: =Date(AddMonths(Max(Date),-11), 'MMM YYYY')         

vMaxDate12 - After:    Date(AddMonths(Max(Date),-11), 'MMM YYYY')           

However, I still have no idea how to write that in an expression. I tried what's written below and a few other ways, but I could not get it to work.

Sum({<Date = {">=$(vMaxDate12)"}>} Units)

Side note: I had {$<Year>} in my equation, because originally I had wanted rolling 12 months where if you highlighted 2007-2012, it would still calculate rolling 12 months for 2007 based on 2006 data, even though it was not selected. You are right though, it is not relevant for what I am trying to do now. Thanks for pointing it out. I think taking the slower approach and testing each part is a much better strategy.

Thanks again!

[stevedark]

Hi Abraham,

I should have spotted the missing equals - sorry about that!

Whether variables have equals signs in front of them or not is very interesting.  When you use a variable with a dollar sign it basically copies and pastes the content of the variable into the expression when you do it.

When your variable starts with an equals sign it evaluates the expression in the variable and returns the result.  Without the equals it returns the text content of the variable.

So, with equals vMaxDate will return:

Sep 2014

Without an equals it will return:

Date(Max(Date), 'MMM YYYY')

So, if you have a text box with the following:

=$(vMaxDate)

The first result will return null, as without quotations Sep 2014 makes no sense, but the expression returned from the variable without an equals does make sense - and will be evaluated by the text box.

You will be able to see the difference if you encapsulate the variable in quotations, so write the expression as:

="$(vMaxDate)"

Then try adding and removing the equals in the variable.  Note that I used double quotes rather than single, as the variable content without quotes contains single quotes (which would break and cause a null to be returned).

So, in order to make the expression work, I would put the equals sign at the start of the variable definition (be sure not to have rogue spaces here) and use it in the expression like this:

Sum({<Date = {">=$(vMaxDate12)"}>} Units)

This will however only work if Date itself is in the format MMM YYYY, if it is formatted with days as well it will fail.  You may need to format the variable differently.  To see how Date is formatted add a List Box to your app with Date in it - it will show in the underlying format.

All that said, if it is working for you whilst evaluating the max date within the Set Analysis itself then you may want to just leave it.  It is worth getting your head around the difference of having an equals at the start of a variable or not.

Hope that all makes some kind of sense.

Cheers,

Steve

[#5]

Thanks for writing that out Steve. That was an area of Qlikview I did not quite understand and it has been causing me problems where I have pretty much been guessing if it is single or double quotes, or if there is an equals or not. This makes a lot more sense.

I will definitely use this conversation as a reference guide in future. Also, I will eventually try to implement your blog recommendation, although at a glance it looks a bit beyond my skill level.

Thanks again. I really appreciate you taking the time to help me.

[stevedark]

No problems.  Give me a shout if you need a hand implementing the MAT code from the example document in your own model.

Steve