Problem with assigning result of an expression to a variable

[#1]

The following expression works fine when I use it in a QlikView control:

=Sum({$<fact_ReconciliationStockValueSAP.sap_stock_value_date = {'$(=Date(Min(fact_ReconciliationTransactions.transaction_date) - 1))'}>} fact_ReconciliationStockValueSAP.stock_value)

but it does not work when I try to use it a script e.g. like this

LET vTest = Sum({$<fact_ReconciliationStockValueSAP.sap_stock_value_date = {'$(=Date(Min(fact_ReconciliationTransactions.transaction_date) - 1))'}>} fact_ReconciliationStockValueSAP.stock_value)

I have also tried the $-expansion but did not manage to get the expression to function. I always get the following error:

Aggregation expressions not allowed in GROUP BY clause

LET vTest = Sum({$<fact_ReconciliationStockValueSAP.sap_stock_value_date = {'(internal error)'}>} fact_ReconciliationStockValueSAP.stock_value)

What am I doing wrong?

Thanks,

Thomas

[gussfish · Accepted solution]

At issue is that the scripting language is deceptively similar to the UI expressions language - but not the same! The two differences that are tripping you up are

1. set expressions are not supported in scripts

2. aggregation functions are only for evaluation in the context of LOAD or SELECT statements.

So, if you really want to get an aggregated value into a variable, you'd need to construct a LOAD statement that calculates the aggregation you want and has a WHERE clause to do the filtering that's equivalent to your set expression, and that results in a single record containing that value

(i.e.

MyAggr:

LOAD SUM(fact_ReconciliationStockValueSAP.stock_value)

WHERE ;

) ;

and then use peek() to get the value into your variable.

[#3]

Thank you so much! It never occured to me that I'm trying something that cannot work...