Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I need to use the following expression as a Calculated Dimension:
=FirstSortedValue( aggr(sum(stkh_movement_quantity),stkh_product,stkh_movement_date), -aggr(stkh_movement_date,stkh_product,stkh_movement_date)))
The aim is to give only the most recent movement quantity, based on the latest movement date. It works fine as an expression, however when used as a Dimension I get the //Error in Calculated Dimension. Any ideas where I'm going wrong?
Many thanks for any help.
You may just have an extra bracket at the end
That was just bad copying and pasting, the extra bracket isn't there on the actual doc. Good spot though...
The expression looks ok.. one thing to check is see if that date field is actually seen as date in qlikview.. Iv used a similar formula as an expression and it works fine but not as a dimension
=FirstSortedValue( aggr(sum(stkh_movement_quantity),stkh_product,stkh_movement_date), -aggr(stkh_movement_date,stkh_product) )
try the above
Gives the same error unfortunately...
Can you give a small sample data with a table n QV .. scramble the data if need be.
Regards
Hi David
Although the context is corect i dont think it is possible to use the FirstSortedValue in a calculated Dimension, as it is designed to return the first value of expression itterated over a dimension.
To test this just copy your calculated dimension calculation into a straight table expression and use a single dimension and if it works without errors means that function "First Sorted Value" is not appropriate to be used within a calculated dimension
Thanks for your help Nam. The calculation definitely works fine as an expression. Does anybody know of a work around to use this as a dimension?
Hi
My proposed solution would be create a flag in the script to identify the latest date for each product and use that flag in set analysis within the calculated dimension.
Regards