topic Re: Simple (?) Index in QlikView

Simple (?) Index

Thu, 17 Nov 2011 14:49:04 GMT

Hi experts!

OK... so I've got a line on a chart that does this across a time axis...

Sum(LineValue)/Sum(Quantity)

So far, so easy peasy.

Now I want another line that does this...

(Sum(LineValue)/Sum(Quantity))/(FIRST(Sum(LineValue)/Sum(Quantity))

where the denominator is the first calculated value - and the selection of "FIRST" should be variable as the earliest in the current dataset, not defined - and not necessarily the "First" function in QV).

Should be easy surely... but I just can't seem to crack some of what I expect to be the basic QV functions.

Help yet again, please.

QlikView for Windows Version 9.00.7646.9 SR6

Re: Simple (?) Index

johnw — Thu, 17 Nov 2011 22:27:40 GMT

I'm still unclear on what you mean by first, but you MAY be looking for the top() function:

(Sum(LineValue)/Sum(Quantity))/(Top(Sum(LineValue)/Sum(Quantity))

Re: Simple (?) Index

Fri, 18 Nov 2011 08:57:49 GMT

Thanks for your help John,

For clarity (I hope), my dataset is split into periods, and I want to index each period based on the first period in the selected data.

I'll give Top a try .

Dave

Re: Simple (?) Index

Fri, 18 Nov 2011 10:42:21 GMT

Making progress, but still got a couple of issues, which I will try to explain.

Thanks John,

Using Top, I have managed to achieve the calculation I needed (defined as an Expression called "Index") as follows:

((Sum (LineValue)/Sum (Quantity))/(Top(Sum (LineValue))/Top(Sum (Quantity))))-1

But this seems only to work in a Pivot Table.

Perhaps this is because of the Dimensions that I am using?

I have a time Dimension called YearPeriod, and a Drill Down Group comprising Group, Type and Product.

The Pivot Table works if I have the Dimensions in the order "Group Drill Down" and "YearPeriod".

It seems the number of expressions is immaterial, because, as I say, the table does everything I require.

However, if I change properties and make this into a Line Chart the Index Expression fails and the drill down ceases to work also.

Any help gratefully received.