SID-01439: Result of sum is showing dates?

Status:	Answered	TWiki version:	5.1.1	Perl version:
Category:	SpreadSheetPlugin	Server OS:	ubuntu	Last update:	14 years ago

Hi! I'm wondering if might be doing something wrong the result of a sum is showing the date (year) of the formfield value.

%SEARCH{"[SWO]{3}-[0-9]{5};\bCategoryInvoice\b" type="regex" nonoise="on" nototal="on" excludetopic="*Template,TaskDB,*Task" web="DB" scope="text" separator="," format="|$percntIF{\" '$percntCALC{$FORMATTIME($TIME($formfield(Invoice date)), $year-$month)}$percnt' = '$percntSERVERTIME{$year-$mo}$percnt' \" then=\"$percntGET{\"$topictotal\"}$percnt\" else=\"0\"}$percnt|"}%
| %CALC{$SUM($ABOVE())}% |

result:

showing the "year" of the formfield value instead of 0 in the last row.

-- MichaelSevero - 2012-03-28

Discussion and Answer

Just a quick reply without reading the details: You might run into an evaluation order issue. The CALC is not handled in the normal inside-out/left-to-right variable evaluation order, it is evaluated later. If you want to run the IF after CALC you need to embed the escaped IF in the CALC, such as %CALC{$NOP(%)IF{...}$NOP(%)}%.

-- PeterThoeny - 2012-03-28

Awesome!!

Thanks Peter for clearing it. What I did was use the "if" function of the CALC instead of the regular "IF statement"

My revision:

%SEARCH{"[SWO]{3}-[0-9]{5};\bCategoryInvoice\b" type="regex" nonoise="on" nototal="on" excludetopic="*Template,TaskDB,*Task" web="DB" scope="text" separator=","
format="|$percntCALC{$IF($FORMATTIME($TIME($formfield(Invoice date)), $year$month) == $percntSERVERTIME{$year$mo}$percnt, $percntGET{\"$topictotal\"}$percnt, 0)}$percnt|"}%
|%CALC{$SETM(swosalestotal, + $SUM($ABOVE()))}%|

again, thank you

-- MichaelSevero - 2012-03-28

I am glad it worked out. And thanks for reporting back for others to learn.

-- PeterThoeny - 2012-03-29

If you answer a question - or someone answered one of your questions - please remember to edit the page and set the status to answered. The status selector is below the edit box.