Error in calculated dimension (Don't know why)

[#1]

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

[jerem1234 · Accepted solution]

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!

[#3]

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

[#4]

Anyway thanks!!!

[jerem1234]

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!

[#6]

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

[jerem1234]

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.

[#8]

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?

[#9]

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

[jerem1234]

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'??