topic Re: Show rolling 12 Months only for last 12 months. in QlikView

Show rolling 12 Months only for last 12 months.

Mon, 29 Sep 2014 20:56:27 GMT

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?

Re: Show rolling 12 Months only for last 12 months.

afurtado — Mon, 29 Sep 2014 22:53:09 GMT

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

Re: Show rolling 12 Months only for last 12 months.

Tue, 30 Sep 2014 12:50:11 GMT

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.

Re: Show rolling 12 Months only for last 12 months.

stevedark — Tue, 30 Sep 2014 12:56:52 GMT

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

Re: Show rolling 12 Months only for last 12 months.

Tue, 30 Sep 2014 12:59:41 GMT

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.

Re: Show rolling 12 Months only for last 12 months.

Tue, 30 Sep 2014 13:00:13 GMT

Thanks Steve, I will read through it.

Re: Show rolling 12 Months only for last 12 months.

stevedark — Tue, 30 Sep 2014 13:08:28 GMT

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

Re: Show rolling 12 Months only for last 12 months.

Tue, 30 Sep 2014 14:12:50 GMT

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)

Re: Show rolling 12 Months only for last 12 months.

stevedark — Tue, 30 Sep 2014 14:35:19 GMT

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

Re: Show rolling 12 Months only for last 12 months.

Tue, 30 Sep 2014 14:49:09 GMT

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.

Re: Show rolling 12 Months only for last 12 months.

stevedark — Tue, 30 Sep 2014 16:45:50 GMT

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

Re: Show rolling 12 Months only for last 12 months.

Tue, 30 Sep 2014 19:27:27 GMT

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!

Re: Show rolling 12 Months only for last 12 months.

stevedark — Tue, 30 Sep 2014 19:57:27 GMT

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

Re: Show rolling 12 Months only for last 12 months.

Tue, 30 Sep 2014 20:19:03 GMT

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.

Re: Show rolling 12 Months only for last 12 months.

stevedark — Tue, 30 Sep 2014 21:45:28 GMT

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

Steve