topic Re: Expression based on a AutoNumber() does not calculate in QlikView

Expression based on a AutoNumber() does not calculate

yzzy2bones — Sat, 16 Nov 2024 00:31:47 GMT

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!

Re: Expression based on a AutoNumber() does not calculate

lironbaram — Sun, 07 Jun 2020 16:30:42 GMT

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

Re: Expression based on a AutoNumber() does not calculate

rubenmarin — Sun, 07 Jun 2020 16:31:32 GMT

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

Re: Expression based on a AutoNumber() does not calculate

Brett_Bleess — Wed, 01 Jul 2020 14:33:31 GMT

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