Expression based on a AutoNumber() does not calculate

[yzzy2bones]

Hi,

I needed to do a calculation that requires Quarters to be in a numeric form, so I used in script this formula in the master calendar:

num#(AutoNumber(QuarterName(TempDate))) as Qtr_Key

But when using the Qtr_Key in an expresion I get - .

This is the expresion that I needed Qtr_Key:

 if(sum({<Qtr_Key{$(=(Max(Qtr_Key)))}>}(TotalREVENUE-TotalCOST))<0,1,0)+/*q2*/if(sum({<Qtr_Key={$(=(Max(Qtr_Key)-1))}>}(TotalREVENUE-TotalCOST))<0,1,0)

Basically, I am trying to calculate if the last 2 quarters have been unprofitable and if so, to add a count on how many of these 2 have been unprofitable.

Any help on why would I get - ? 

Thank you!

[lironbaram]

hi 

did you try to replace the num# to num 

it seems your field is set tot text this is why max function doesn't work 

[rubenmarin · Accepted solution]

Hi, first create a data table to confirm that Qtr_key is assigned as expected. And in a text box try =Max(Qtr_Key) to confirm that it returns the expected result.

If you use only sum({<Qtr_Key{$(=(Max(Qtr_Key)))}>}(TotalREVENUE)), it returns data? If this works maybe it's beacuse TotalREVENUE and TotalCOST are in different rows, so row by row there will be a null() in the substraction, and number-null()=null()... but in this case the 'if' should be going by the else.

You can use alt o convert nulls to zero: sum({<Qtr_Key{$(=(Max(Qtr_Key)))}>}(Alt(TotalREVENUE,0)-Alt(TotalCOST,0)))

[Brett_Bleess]

You have two good responses on this thread, we would greatly appreciate if you would close out the thread if one or both of those threads helped you solve things by using the Accept as Solution button on the post(s) that helped.  This gives the poster credit for the help and lets other Community Members know what the issue was.  If you did something else, you can post that and use the button on it to mark that as the solution too.

Regards,
Brett