topic Re: Error in calculated dimension (Don't know why) in QlikView

Error in calculated dimension (Don't know why)

Wed, 05 Nov 2014 16:17:06 GMT

Hi everybody. I was working with this dimension, and it was working.

=IF(EndDate-Date <60, PurgeChar(Class(EndDate - Date ,15,'a'),'>=<') , '+ 60')

Now, i have to replace the "EndDate" With this

timestamp(If([(QUANTITY1- QUANTITY2] = 0, Aggr(MAX(AutoDate), CodigoWeb), '-')) (this expresion works in the expression part)

And when i replace this in the calculate dimension it doesn´t works! I doesnt make sense, the sintax is OK

i'll put an example in the QV!

Please help me i´ve been trying to fix it for a long time

Greetings

Re: Error in calculated dimension (Don't know why)

jerem1234 — Wed, 05 Nov 2014 17:59:37 GMT

It is because you can't have aggregation functions as a calculated dimension. You have an if statement, but inside the if statement you have the following: sum(CantidadAdjuntada) - sum(CantidadEntregada). It doesn't know which dimension to do the sums by, since it is not specifed. Hence why it thinks you are using an aggregation as a calculated dimension. So what you want to do is wrap your function in an aggr() like:

aggr(IF(If((sum(CantidadAdjuntada) - sum(CantidadEntregada) = 0), Aggr(MAX(AutoDate), CodigoWeb), '-') - Date <60, PurgeChar(Class(If((sum(CantidadAdjuntada) - sum(CantidadEntregada) = 0), Aggr(MAX(AutoDate), CodigoWeb), '-') - Date ,15,'a'),'>=<') , 'Mas de 60'), CodigoWeb)

Hope this helps!

Re: Error in calculated dimension (Don't know why)

Wed, 05 Nov 2014 18:17:37 GMT

Oh my god, that's complicated. Actually, it works, but i didnt understand what's going on.

Re: Error in calculated dimension (Don't know why)

Wed, 05 Nov 2014 18:20:13 GMT

Anyway thanks!!!

Re: Error in calculated dimension (Don't know why)

jerem1234 — Wed, 05 Nov 2014 18:26:29 GMT

Well, this formula might be a little nicer on the eyes:

=aggr( if(sum(CantidadAdjuntada) - sum(CantidadEntregada) = 0, if(MAX(AutoDate)-Date<60,PurgeChar(Class(MAX(AutoDate)-Date,15,'a'),'>=<'), 'Mas de 60')), CodigoWeb)

Im guessing you do not want to sum the CantidadAdjuntada of the CodigoWeb that doesn't have sum(CantidadAdjuntada) - sum(CantidadEntregada) true right?

In your expression now, i think it is being lumped into 'Mas de 60'. So use the expression above and check suppress when value is null.

Hope this helps!

Re: Error in calculated dimension (Don't know why)

Wed, 05 Nov 2014 18:38:23 GMT

The two expressions have different results . Sory but my english is not good. Did you see my example? Yeah you are right, In a few words i have to do this:

Autodate: When the Cant1 - Cant 2 is not 0, i have to choose the max date of the codigoweb.

timestamp(If([(QUANTITY1- QUANTITY2] = 0, Aggr(MAX(AutoDate), CodigoWeb), '-'))

And then i have to use this part of the function to use as dimension

=IF(EndDate-Date <60, PurgeChar(Class(EndDate - Date ,15,'a'),'>=<') , '+ 60') and in the part of endate, i have to put all that another date

Isnt your first expression right??

Thank you!!!

Re: Error in calculated dimension (Don't know why)

jerem1234 — Wed, 05 Nov 2014 18:50:56 GMT

Yes, they give different results, im just trying to deduce which is right in your case.

Hmm I guess my question revolves around this:

If([(QUANTITY1- QUANTITY2] = 0, Aggr(MAX(AutoDate), CodigoWeb), '-')

If [(QUANTITY1- QUANTITY2] = 0, then according to your expression you want the max date.

If [(QUANTITY1- QUANTITY2] does not equal zero, according to your expression, it gets the string value '-'.

Is that right?

Cause then when you use it in your expression, you have EndDate - Date, which if you have [(QUANTITY1- QUANTITY2] not 0, then it will try to evaluate '-' - Date, which is a string minus a number.

Re: Error in calculated dimension (Don't know why)

Wed, 05 Nov 2014 18:56:17 GMT

Yeah that´s right. I wanna the max date but not the max date of AAALLL my values, my max date grouped by the num_webs !

I think the second expression is the right one. Am i right?

Re: Error in calculated dimension (Don't know why)

Wed, 05 Nov 2014 19:04:16 GMT

And if i am right i have another little question and im finished . I have doubts if i have to aggr by num_web and by date too

Re: Error in calculated dimension (Don't know why)

jerem1234 — Wed, 05 Nov 2014 19:04:42 GMT

Hmm what do you want when [(QUANTITY1- QUANTITY2] != 0??

In your example, for CodigoWeb = 3, the sum(CantidadAdjuntada) - sum(CantidadEntregada) = 2, what date do you want to use in that case OR do you not want to sum it at all if thats the case? (In your straight table, it is the one with the value of '-' instead of a date.

What class should CodigoWeb = 3 be in your pie chart??which one of '0 a 15', '15 a 30', or 'Mas de 60'??

Re: Error in calculated dimension (Don't know why)

Wed, 05 Nov 2014 19:10:55 GMT

Ok, when the [(QUANTITY1- QUANTITY2] != 0 i wanna have the "-" This is because the items aren´t delivered yet and there isn´t a finish date. And then i´d put "dont select nulls" (because the client don't wanna see the items that aren´t delivered yet)

Thank you!

Re: Error in calculated dimension (Don't know why)

jerem1234 — Wed, 05 Nov 2014 19:16:27 GMT

Okay, so that should not be summed in that case. Got it. So use my second expression.

Just to clarify, when you type in '-', that does not make the value null, it makes it a string equal to a dash. If you want to truly have it null(), either user null(), or do not add the second parameter to the if statement: if([(QUANTITY1- QUANTITY2], max(Date))

If you don't add a third parameter to the if statement, it automatically sets whats not true to a null value.

Re: Error in calculated dimension (Don't know why)

Wed, 05 Nov 2014 19:24:29 GMT

Ok man, thank you!

I see now that i dont have the "-" in the second expression.

I have one question but if you prefeer i can´t put it in another thread.

Now i´m groupping by the num_web( is the "primary key" of the table). Should i aggregate by the dates too? because i dont know if the expression is taking the dates when i have 4 or 5 dates in the same num_web

Sory but this expression is a bit too dificult to mee.

Cheers.

Re: Error in calculated dimension (Don't know why)

jerem1234 — Wed, 05 Nov 2014 19:29:45 GMT

Can you post an example of what your data looks like? Just using the fields CodigoWeb, EndDate, and StartDate

In this format:

CodigoWeb, EndDate, StartDate

1, 10/02/2014, 14/02/2014

1, 13/02/2014, 17/02/2014

...

Is that what it looks like? If not, please post an example.

Re: Error in calculated dimension (Don't know why)

Wed, 05 Nov 2014 19:37:44 GMT

Of course! For example:

CodigoWeb	Fecha	Fecha_Fin
		-
23	06/02/2014	-
24	26/02/2014	-
25	26/02/2014	-
34	28/02/2014	13/05/2014
33	28/02/2014	13/05/2014
27	28/02/2014	13/05/2014
26	28/02/2014	13/05/2014
28	28/02/2014	13/08/2014
29	31/03/2014	-
30	14/04/2014	27/05/2014
31	14/04/2014	-
35	14/04/2014	-
16	24/04/2014	09/05/2014
17	24/04/2014	15/05/2014
18	24/04/2014	09/05/2014
19	24/04/2014	15/05/2014
20	24/04/2014	27/05/2014
21	24/04/2014	29/05/2014

Re: Error in calculated dimension (Don't know why)

jerem1234 — Wed, 05 Nov 2014 19:46:46 GMT

Hmm i don't see any multiple dates for the web. Can you elaborate more on what u mean with "because i dont know if the expression is taking the dates when i have 4 or 5 dates in the same num_web"

Can you show an example of that in your data?

Re: Error in calculated dimension (Don't know why)

jerem1234 — Wed, 05 Nov 2014 21:17:49 GMT

Just to be save, I'd also put min around your Date field as well. If you say there are multiple dates for a CodigoWeb, then you'd probably want the longest stretch between any there. So:

=aggr( if(sum(CantidadAdjuntada) - sum(CantidadEntregada) = 0, if(MAX(AutoDate)-MIN(Date)<60,PurgeChar(Class(MAX(AutoDate)-MIN(Date),15,'a'),'>=<'), 'Mas de 60')), CodigoWeb)

So this will assign a class to the length of time from the min Date to the max AutoDate for each CodigoWeb. If that is what you are looking for

Hope this helps!

Re: Error in calculated dimension (Don't know why)

Fri, 07 Nov 2014 13:27:43 GMT

Sory i´ve just seen this. I´m proving it now. In this case is the same with or without the MIN.

Thank you. You helped me a lot, Please help me the next time! haha.

Re: Error in calculated dimension (Don't know why)

Fri, 07 Nov 2014 17:14:16 GMT

If you can, help me with this please

Doubts about an If statements and replacing values.