10 Replies Latest reply: Feb 5, 2014 1:50 PM by Abhi Hariharan

# 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.

• ###### Re: Error in Calculated Dimension

You may just have an extra bracket at the end

• ###### Re: Error in Calculated Dimension

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

• ###### Re: Error in Calculated Dimension

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

• ###### Re: Error in Calculated Dimension

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

try the above

• ###### Re: Error in Calculated Dimension

Gives the same error unfortunately...

• ###### Re: Error in Calculated Dimension

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

Regards

• ###### Re: Error in Calculated Dimension

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

• ###### Re: Error in 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?

• ###### Re: Error in Calculated 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

• ###### Re: Error in Calculated Dimension

Hey guys,

I am given a task to calculate the frequency of calls across a territory. If the rep called a physician regarding the sale of the product 5 times, then frequency is 5 and HCP count is 1....I generated frequencies from 1 to 124 in my pivot table using a calculated dimension which is working fine. But my concern is :

My manager wants frequencies till 19 in order from 1..2..3..4...5..6.....19...

And from the frequency 21-124 as 20+.

I would be grateful if someone helps me with this.....Eager for the reply....