3 Replies Latest reply: Aug 17, 2015 12:07 AM by jagan mohan rao appala

# Formula on both sides of the expression in the expression editor

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

• ###### Re: Formula on both sides of the expression in the expression editor

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:

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

camnt,

ramnt

FROM ...;

Then your expression should be straight forward using this field.

• ###### Re: Formula on both sides of the expression in the expression editor

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

• ###### Re: Formula on both sides of the expression in the expression editor

Hi,

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

Regards,

jagan.