Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to calculate the sum of the debits for the current month using the formula below. I'm not sure what I'm doing wrong here because I get only 0s with this formula.
sum({$<GL_Month={"=$(=Date(Floor(MonthStart(Max(ActualsDate))),'MMM-YYYY'))"}>}Accounted_Debit)
I want to calculate the value for the previous month as well.
GL_Month is the month column that is derived in the script with the following formula :
Date(MonthStart("ActualsDate"), 'MMM-YYYY') as GL_Month,
Like this.
... {"=$(=Date(Floor(MonthStart(Max(ActualsDate))-1),'MMM-YYYY'))"} ...
I tried it with the previous month's calculation and still get 0s, same as the issue I'm facing with the current year
Is ActualsDate a timestamp field? You're using Floor() to round numbers in one place but not in GL_Month. Floor() rounds down numbers, so not using it in GL_Month could affect how dates are compared.
It could ne that you are evaluating when all you need is the string value. Try this
sum({$<GL_Month={'$(=Date(Max(ActualsDate),''MMM-YYYY''))'}>}Accounted_Debit)
Notice the double '' (it is not not single ")