
Re: Using Above/Below functions in Expressions on a table
John Witherspoon Aug 1, 2011 6:40 PM (in response to perry.qv)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
perry.qv Aug 1, 2011 7:19 PM (in response to John Witherspoon )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 Witherspoon Aug 1, 2011 7:39 PM (in response to perry.qv)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.

testTableDrivenCalculations2.qvw 122.2 K

Re: Using Above/Below functions in Expressions on a table
John Witherspoon Aug 1, 2011 8:11 PM (in response to John Witherspoon )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.

testTableDrivenCalculations3.qvw 126.2 K

testTableDrivenCalculations4.qvw 124.0 K

Re: Using Above/Below functions in Expressions on a table
perry.qv Aug 1, 2011 8:22 PM (in response to John Witherspoon )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.





Re: Using Above/Below functions in Expressions on a table
sailor Aug 9, 2011 7:32 AM (in response to perry.qv)John is probably referring to this solution http://community.qlik.com/thread/18416

Re: Using Above/Below functions in Expressions on a table
John Witherspoon Aug 9, 2011 12:51 PM (in response to sailor )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.


Re: Using Above/Below functions in Expressions on a table
sailor Aug 10, 2011 6:01 AM (in response to perry.qv)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?