8 Replies Latest reply: Aug 10, 2011 6:01 AM by sailor RSS

    Using Above/Below functions in Expressions on a table

      HI,

       

      I'm having trouble using the Above and Below functions in a straight table. Basically there is a number of different Formula Expressions (being my above/below formulas) and each is specific to a particular dimension value. i.e. Each dimension value that has a formula only has one formula.

       

      So in it's most basic form my table might look like;

       

      Dimension, Amount, Formula Expression

      Sales, 2000, -

      Additional Sales, 1000, -

      Total Sales, 3000, above(sum(sales), 2)+above(sum(sales), 1)

      Sales Quantity, 30, -

      Average Price, 100, (above(sum(sales),4)+above(sum(sales),3))/above(sum(sales),1)

       

      NB: The 3000 and 100 are the values i would expect to see.

       

      If i show the formulas as they are (i.e. not evaluating them) they show the correct fomula on both the bold lines as i have writen above.

      If i evaluate the formulas however by using $(=[Formula Expression]) it shows '-' for both of the calculated dimensions.

      If however i filter the table to only contain 1 Dimension with a Formula Expression (i.e. select lines Sales through Sales Quantity), it calculates correctly. It's only when there is more than 1 dimension displayed on the table with a Formula Expression that the evaluated value shown is '-'.

       

      Does anyone know how I can achieve the result needed?

       

      Thanks in advance.

       

      Alan

        • Re: Using Above/Below functions in Expressions on a table
          John Witherspoon

          Dollar sign expansion is done outside of the context of the dimensions.  Since there are multiple values for the formula expression, you can expect it to return null UNLESS a single dimension is selected, because only when a single dimension is selected does your formula have a single value that can then be evaluated.

           

          I don't have the solution for you, unfortunately.  I could swear I've seen a clever solution, but I don't seem to have it among my saved examples, and I can't think how you'd do it.

           

          A workaround might be to do it with a nested if() or a pick(match()).

           

           

          if(Dimension='Total Sales'  ,above(sum(sales),2)+above(sum(sales),1),
          if(Dimension='Average Price',above(sum(sales),4)+above(sum(sales),3))/above(sum(sales),1)))

           

          pick(match(Dimension,'Total Sales','Average Price')
          ,above(sum(sales),2)+above(sum(sales),1)
          ,above(sum(sales),4)+above(sum(sales),3))/above(sum(sales),1))

            • Re: Using Above/Below functions in Expressions on a table

              Thanks John, both of those solutions worked and would be very useful if my table was as simple as my example above. Unfortunately we had shied away from hard coding the formuals because of both the number needed and the reasons outlined below.

               

              The reason i would prefer the Formula Expression not to be hard coded is becasue we are retrieving it from a template inside our data source (our ERP system) so users can manage (make, alter, etc.) the table layouts in the ERP and the template is then exported to Qlikview and interpreted as appropriate.

               

              We therefore enter the above/below formulas in the template in our ERP system and give it a 'Formula' tag so when its imported into Qlikview we had our Expressions as;

                   if([Dimension Type]='Formula'

                        ,$(=(Formula Expression))

                        ,sum(sales)

               

              As you say however this only works if there is only one formula being calculated in the table. I do know however that it is retrieving the correct formula for each dimension as if we dont use the dollar sign to expand the Formula Expression, then each dimension row that is a formula has the correct Formula Expression displayed as text. It's as if i need to validate the dimension somehow first before retrieving the Formula Expression of that dimension.

               

              Hopefully someone has resolved this one way or another and will respond.

               

              Thanks again

            • Re: Using Above/Below functions in Expressions on a table

              John is probably referring to this solution http://community.qlik.com/thread/18416

              • Re: Using Above/Below functions in Expressions on a table

                 

                The problem comes with using a second dimension. The above() function does then know no further rows above, because it looks for a row above within the same group, based on the sort order of the dimensions.

                 

                 

                 

                The straight will look like this with the pick function:

                 

                Dimension

                Line No

                Amount

                Formula Expression (not working)

                Formula Expression (working)

                Sales

                1

                2000

                pick([Line   No],sum(sales),sum(sales),above(sum(sales),2)+above(sum(sales),1)
                ,sum(quantity),(above(sum(sales),4)+above(sum(sales),3))/above(sum(quantity),1))

                pick([Line No],sum(sales),sum(sales),above(TOTAL sum(sales),2)+above(TOTAL sum(sales),1)
                ,sum(quantity),(above(TOTAL sum(sales),4)+above(TOTAL sum(sales),3))/above(TOTAL sum(quantity),1))

                Additional Sales

                2

                1000

                pick([Line   No],sum(sales),sum(sales),above(sum(sales),2)+above(sum(sales),1)
                ,sum(quantity),(above(sum(sales),4)+above(sum(sales),3))/above(sum(quantity),1))

                pick([Line No],sum(sales),sum(sales),above(TOTAL sum(sales),2)+above(TOTAL sum(sales),1)
                ,sum(quantity),(above(TOTAL sum(sales),4)+above(TOTAL sum(sales),3))/above(TOTAL sum(quantity),1))

                Total Sales

                3

                3000

                pick([Line   No],sum(sales),sum(sales),above(sum(sales),2)+above(sum(sales),1)
                ,sum(quantity),(above(sum(sales),4)+above(sum(sales),3))/above(sum(quantity),1))

                pick([Line No],sum(sales),sum(sales),above(TOTAL sum(sales),2)+above(TOTAL sum(sales),1)
                ,sum(quantity),(above(TOTAL sum(sales),4)+above(TOTAL sum(sales),3))/above(TOTAL sum(quantity),1))

                Sales Quantity

                4

                30

                pick([Line   No],sum(sales),sum(sales),above(sum(sales),2)+above(sum(sales),1)
                ,sum(quantity),(above(sum(sales),4)+above(sum(sales),3))/above(sum(quantity),1))

                pick([Line No],sum(sales),sum(sales),above(TOTAL sum(sales),2)+above(TOTAL sum(sales),1)
                ,sum(quantity),(above(TOTAL sum(sales),4)+above(TOTAL sum(sales),3))/above(TOTAL sum(quantity),1))

                Average Price

                5

                100

                pick([Line   No],sum(sales),sum(sales),above(sum(sales),2)+above(sum(sales),1)
                ,sum(quantity),(above(sum(sales),4)+above(sum(sales),3))/above(sum(quantity),1))

                pick([Line No],sum(sales),sum(sales),above(TOTAL sum(sales),2)+above(TOTAL sum(sales),1)
                ,sum(quantity),(above(TOTAL sum(sales),4)+above(TOTAL sum(sales),3))/above(TOTAL sum(quantity),1))

                 

                 

                 


                When a TOTAL qualifier is added (fifth column), the entire column will be regarded as one column segment. The second dimension (here [Line No.]) can then also be flagged as hidden, because it is used only to pick the desired expression.

                 

                 

                 

                You might simplify the pick expression by using match, if there is a kind of default expression for most of the rows. I’m wondering if there is a max character length for expressions?