Using Above/Below functions in Expressions on a table

[#1]

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

[johnw]

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))

[#3]

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

[johnw]

OK, as an extension of the workaround, you can generate the if() or pick(match()) as a variable.  In the attached example, I use a table of metrics vs. expressions to generate the pick(match()) version.  This should work the same with an actual data table as input instead of the inline load.

It does seem there should be a better way, but this works, at least in a simple example.

Apologies if I'm still not quite grasping the situation.  I don't have your dimension type and default expression in the example, but they seem like they'd be easy to add.

[johnw]

Here's the same basic idea, but trying to guess at your basic data structure.  It doesn't seem right to use sum(Sales) as your Sales Quantity, so I suspect I misunderstood something in that regard.  Anyway, it's generating the numbers for your example.

Edit:  To simplify the formulas and avoid recalculating data, you could name the column "Result" and then refer to that in the above().  See version 4, attached.

[#6]

Thanks John,

Those examples look like they could be a good solution for what we are after. I will get into them in the coming days, make the appropriate changes for our data and let you know how we get on.

[#7]

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

[johnw]

sailor wrote:

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

Hmmm, I don't think so.  Not to say that it isn't a clever solution, but it's simply another variation of the same pick() solution we've been discussing.  I thought I had once seen a way to actually make QlikView evaluate the expressions directly.  I could be wrong.  Maybe it's a case where it seems so much that there should be a way that I imagine I once saw a way, even though I didn't.  Who knows. 

[#9]

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?