Formula on both sides of the expression in the expression editor

[#1]

Hi,

I have 3 columns: bdate, camnt and ramnt. I am trying to calculate the MTD sales based on these values.

sales = (camnt - ramnt)

However bdate is in a different format and requires a formula to be converted to date format:

bdate1 = date(floor(bdate/ 86400 + 25569))

Now my MTD expression looks something like this:

Sum({<$(=date(floor(bdate/ 86400 + 25569))) = {">=$(=MonthStart(max(date(floor(bdate/ 86400 + 25569))))) <=$(=(max(date(floor(bdate/ 86400 + 25569)))))"}>} (camnt-ramnt))

However, all I get is the sales for that particular date which I have selected and not the cummulative sales of that month till that date. Can someone please tell me what am I doing wrong here?

Regards,

Saurav

[swuehl · Accepted solution]

You can't use an expression instead of a field name on the left side of equal sign of a set analysis set modifier.

Transform your date values when you load your field in the script:

LOAD

     date(floor(bdate/ 86400 + 25569)) as bdate,

     camnt,

     ramnt

FROM ...;

Then your expression should be straight forward using this field.

[sunny_talwar]

Once you have transformed your bdate using swuehl's suggestion in the script. Try this expression:

Sum({$<bdate = {"$(='>=' & Date(MonthStart(Max(bdate))) & '<=' & Date(Max(bdate)))"}>} (camnt-ramnt))

[jagan]

Hi,

This is not possible, you have to format and arrive a new column in script as swuehl's suggested. 

Regards,

jagan.