3 Replies Latest reply: Jan 5, 2018 10:18 AM by Arthur Resende

# How to create a Index Chart?

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.

Leandro Duarte

• ###### Re: How to create a Index Chart?

first expression will not work in line chart

try to do it this way:

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

• ###### Re: How to create a Index Chart?

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-least-by-most-qlikview-developers/

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

• ###### Re: How to create a Index Chart?

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!