Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Error in Calculated Dimension

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.

10 Replies
nam
Former Employee
Former Employee

You may just have an extra bracket at the end

Not applicable
Author

That was just bad copying and pasting, the extra bracket isn't there on the actual doc. Good spot though...

nam
Former Employee
Former Employee

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

nam
Former Employee
Former Employee

=FirstSortedValue( aggr(sum(stkh_movement_quantity),stkh_product,stkh_movement_date), -aggr(stkh_movement_date,stkh_product) )

try the above

Not applicable
Author

Gives the same error unfortunately...

nam
Former Employee
Former Employee

Can you give a small sample data with a table n QV .. scramble the data if need be.

Regards

nam
Former Employee
Former Employee

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

Not applicable
Author

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?

nam
Former Employee
Former Employee

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