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 · Accepted solution]

I believe this expression should solve it for you:

If

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

[#3]

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.

[#4]

Perhaps you can attach a file showing both examples.

[#5]

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.

[#6]

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.

[#7]

Hmm.

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

[#8]

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.

[#9]

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.

[#10]

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.