topic Re: Error in Calculated Dimension in QlikView

Error in Calculated Dimension

Wed, 26 Jun 2013 12:54:14 GMT

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

nam — Wed, 26 Jun 2013 12:59:19 GMT

You may just have an extra bracket at the end

Re: Error in Calculated Dimension

Wed, 26 Jun 2013 13:03:06 GMT

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

nam — Wed, 26 Jun 2013 13:11:13 GMT

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

nam — Wed, 26 Jun 2013 13:15:13 GMT

=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

Wed, 26 Jun 2013 13:17:49 GMT

Gives the same error unfortunately...

Re: Error in Calculated Dimension

nam — Wed, 26 Jun 2013 13:49:12 GMT

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

Regards

Re: Error in Calculated Dimension

nam — Wed, 26 Jun 2013 14:11:57 GMT

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

Wed, 26 Jun 2013 14:58:46 GMT

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

nam — Wed, 26 Jun 2013 15:03:37 GMT

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

Wed, 05 Feb 2014 18:50:04 GMT

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