Rolling year Base 100

[#1]

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

[#2]

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

[#3]

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,

[#4]

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

[#5]

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

[#6]

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

[#7]

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.