How to create a Index Chart?

[#1]

Dear Colleagues,

I'm trying to create a Index chart (base 100) .

The expression "First" work fine for the pivot table but it is not working in the Line chart.

Index table:

Supplier	2011	2012	2013	2014	2015	2016
a	100	101	102	103	104	105
b	100	99	98	98	97	96
c	100	109	118	127	136	145

Data table:

Supplier	2011	2012	2013	2014	2015	2016
a	110	111	112	113	114	115
b	120	119	118	117	116	115
c	110	120	130	140	150	160

Basically the calculation I want to reproduce in the Line chart is the results of the current year divided by the first year.

Could somebody help me to find the better expresion to create the Line chart? Any good solution would be welcome!

Please find enclosed the example I'm using.

Thanks in advance,

Leandro Duarte

[#2]

first expression will not work in line chart

try to do it this way:

sum(Data)    /  above(sum(Data),rowno()-1) *100

[matt_crowther · Accepted solution]

Leandro,

I've written about Indexed Charts on my blog: http://qvdesign.wordpress.com/2012/05/09/the-indexed-time-series-chart-the-forgotten-technique-at-le...

It includes a .qvw and a 'how to' so it may help you.

I love Indexed Charts - let's get them used more in Qlikview!

All the best,

Matt - Visual Analytics Ltd

Qlikview Design Blog: http://QVDesign.wordpress.com

@QlikviewBI

[Anonymous]

Hi, Matt, nice how to!

Anyway, I have a bit of a complication in this matter.

My "year" dimension have a segments "sub-dimension". Using your guide I'm able to create monthly indexes for all segments.

My problem is that my segments are seasonally affected, so they behavior weirdly within a year period, so I wanted to do indexes based on the YTD calculation (this way my entire base year would be "100", and only in january of the next year it would change, doing monthYTD/basemonthYTD.

If i remove the "TOTAL <Segments>" part of my bottom equation and change the if statement to the base year, All my segments go to 100 to the year 1, but no other years are available.

Do you have any suggestion?

Thanks!