3 Replies Latest reply: Nov 18, 2011 5:42 AM by Dave Wright RSS

    Simple (?) Index

      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
          John Witherspoon

          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

              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

                  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.