topic Rolling year Base 100 in QlikView

Rolling year Base 100

Tue, 12 May 2009 22:15:49 GMT

Hi All,

I am working on a base 100 accumulation, so the concept is the first value is always 100% and the all the others are compare to that first value. In the end you get a line chart showing the monthly trend.

The tricky part is I want this with rolling year values (e.g. 12 months accumulated). Obviously, the first 11 rows do not make sense. So by using this expression:

If(RowNo() >= 12, SalesExp / Top (SalesExp, 12))

Where

SalesExp is Sum (STORE_SALES.QtyPOS), with option "accumulated 12 steps back" selected.

As you can see on this image, it's working except it doesn't take into account the accumulation setting, only the original sum.

Suggestions are appreciated.

Nicolas D

Rolling year Base 100

Fri, 22 May 2009 01:55:42 GMT

I believe this expression should solve it for you:

(RowNo() >= 12, rangesum(above(Sum(Sales),0,12)) / rangesum(top(Sum(Sales),1,12)) )

Rolling year Base 100

Thu, 28 May 2009 07:51:38 GMT

Alright, great stuff, this is working. Thanks a bunch.

I'm trying to add one notch to it, by adding a second dimension, and have a line chart that displays the Base 100 using one bar by DIVISION for example.

I tried this:

if (RowNo() >= 52, rangesum(above(TOTAL Sum(STORE_SALES.QtyPOS),0,52)) /
rangesum(top(TOTAL Sum(STORE_SALES.QtyPOS),1,52)) )

Which work just fine in a pivot table, but wierdly, not in a line bar. Any ideas.

Rolling year Base 100

Thu, 28 May 2009 21:18:44 GMT

Perhaps you can attach a file showing both examples.

Rolling year Base 100

Thu, 28 May 2009 22:47:14 GMT

Sure.

Here is what I want, but in a line chart style (one bar by division), basically, I'm just adding a dimension to the mix. In the pivot table below, it works perfectly:

I've used:

if (RowNo() >= 12, rangesum(above(TOTAL Sum(STORE_SALES.QtyPOS),0,12)) /
rangesum(top(TOTAL Sum(STORE_SALES.QtyPOS),1,12)) )

Basically, just adding the TOTAL qualifier to your solution.

Weirdly, when I swtich to a bar chart, it says NO DATA TO DISPLAY. I though charts were working the same as tables in the background, obviously I am wrong.

I hope this is enough info.

Rolling year Base 100

Tue, 02 Jun 2009 21:42:09 GMT

I believe the basic answer to your second problem is that Chart treat dimnsions differently from tables, in that when you have more than one dimension, they try to use the second one for something different, namely to create multiple series. Perhaps you can attach the qvw file you are working with. Would you like it to have two dimensions on the x axis, or one for the x-axis and one for the series.

Rolling year Base 100

Fri, 05 Jun 2009 05:06:54 GMT

Hmm.

I want one dimension for the x-axis, and the other for the series. I've attached a QVW for details.

Rolling year Base 100

Mon, 08 Jun 2009 21:57:40 GMT

The attached file has what I think you need. It uses the fieldValue function. But be carefull, the documentation says the fieldValue function uses load order as the sort order, which may have unexpected outcomes.

Rolling year Base 100

Thu, 09 Jul 2009 23:49:51 GMT

Hmm, this doesn't really work out, since all division should start at 100%, exactly like the pivot table.

See, what's needed is that each Division (or Department if you will) be seen as how well they perform relative to themselves. Keeping a fair perspective so to speak.

Btw, sorry for the delay, I've been taken up by another project.

Rolling year Base 100

Sat, 11 Jul 2009 04:15:06 GMT

I tried some more and fixed the starting at 100% issue in the table, but still does not work in the chart, sorry. I believe the cause is the interaction between "above(total x)" and "inter field sort order" which seems to behave differently in a chart than in a table. It would be great to get an answer from QlikTech.

Rolling year Base 100

Tue, 14 Jul 2009 20:56:39 GMT

A tricky one heh. Well thanks for the first half of it. I'll post the answer here when I'll find it.

If someone from Qliktech could jump in, that would appreciated...

Rolling year Base 100

Thu, 11 Feb 2010 18:10:19 GMT

Nicolas, did you ever hear any response from QlikTech. I have the same issue, attempting to plot sums (using RangeSum, Above, etc.). Works in a pivot table chart but I cannot get it to work in a line chart.? Thanks,

Rolling year Base 100

Thu, 11 Feb 2010 18:19:45 GMT

Unfortunatly, nobody jumped in.

I haven't had time to work on it either... been busy, you know how it is... too bad, this just seems like a great concept to resale over and over. Provides very good value in any field IMO...

Ah well...

Rolling year Base 100

Thu, 11 Feb 2010 19:50:57 GMT

Thanks! I have submitted a ticket with QlikTech and will post their response. Note I am using QlikView 8.01.

Rolling year Base 100

Fri, 12 Feb 2010 00:09:26 GMT

I sent QlikTech my .qvw and they responded since the charts show no data, my expression is wrong. Well, I knew that as the question was is there a corresponding expression that works for a line chart or is there a bug with line charts? I just figured out the expression required for a line chart that give the same results as the pivot table results. Note I am summing two distinct fields into one. See below:

was able to create an expression that worked in a line chart to give the same results as the expression that worked in a pivot table. Below is the solution which I'll post on the Forum thread where I asked the question. Please close theis ticket as there was no issue with line charts and RangeSum/Above. Thanks,

Pivot Table:

=Rangesum(Sum(NoofClaimsBeforeOnSOP) + Sum(NoofClaimsAfterSOP),Above("Claims"))

Line Chart:

=(If(rowno() > 1,RangeSum(Above(Sum(NoofClaimsBeforeOnSOP),0,rowno())),Sum(NoofClaimsBeforeOnSOP)) + If(rowno() > 1,RangeSum(Above(Sum(NoofClaimsAfterSOP),0,rowno())),Sum(NoofClaimsAfterSOP)))

Rolling year Base 100

Fri, 12 Feb 2010 16:09:04 GMT

One additional note, the line chart expression only works for one dimensional line charts. As soon as I added a second dimension, the results were scewed. The affect of Above() and RowNo() on multimensional charts is discussed in "Help under Chart Inter Record Functions" as well as the corresponding Examples. Based on Help, I would think the expression should work. However, it seemed to nearly'work when using 'TOTOAL' in both the Above() and InRow() functions. By nearly I mean screwed but not nearly as much as when omitting TOTAL.