Help in Set Expression

[Anonymous]

Hi All,

I need to calculate sum of values based on current selections.

My requirement is to calculate difference between Current month and Current month -1 .

My Date format is in YYYY.MM

(sum({<PERIOD_YEAR=,PERIOD={"=$(=max(PERIOD))"},MONTHNAME=,MONTH_NAME={"CA"}>}Gross)

/

sum({<PERIOD_YEAR={"=$(=max(PERIOD))"},MONTHNAME=,MONTH_NAME={"OS"}>}Gross))

-

(sum({<PERIOD_YEAR=,PERIOD ={"=$(=date(MonthStart(Date#(max(PERIOD)),-1),'YYYY.MM')))"},MONTHNAME=,MONTH_NAME=,{"CA"}>}Gross)

/

sum({<PERIOD_YEAR=,PERIOD ={"=$(=date(MonthStart(Date#(max(PERIOD)),-1),'YYYY.MM')))"},MONTHNAME=,MONTH_NAME={"OS"}>}Gross))

I tried the above expression it displaying null values.

Thanks

[sasiparupudi1]

(sum({<PERIOD_YEAR=,PERIOD={"=$(=max(PERIOD))"},MONTHNAME=,MONTH_NAME={"CA"}>}Gross)

/

sum({<PERIOD_YEAR={"=$(=max(PERIOD))"},MONTHNAME=,MONTH_NAME={"OS"}>}Gross))

-

(sum({<PERIOD_YEAR=,PERIOD={"=$(=Date(addmonths(max(PERIOD),-1),'YYYY.MM'))"},MONTHNAME=,MONTH_NAME={"CA"}>}Gross)/

(sum({<PERIOD_YEAR=,PERIOD={"=$(=Date(addmonths(max(PERIOD),-1),'YYYY.MM'))"},MONTHNAME=,MONTH_NAME={"OS"}>}Gross)

[jonathandienst]

Create a test straight table with the same dimensions, and then use one expression for each part of the complete expression to check which parts work, and which parts don't. Then fix the parts that don't work and put the expression back together again.

• sum({<PERIOD_YEAR=,PERIOD={"=$(=max(PERIOD))"},MONTHNAME=,MONTH_NAME={"CA"}>}Gross)
• sum({<PERIOD_YEAR={"=$(=max(PERIOD))"},MONTHNAME=,MONTH_NAME={"OS"}>}Gross)
• sum({<PERIOD_YEAR=,PERIOD ={"=$(=date(MonthStart(Date#(max(PERIOD)),-1),'YYYY.MM')))"},MONTHNAME=,MONTH_NAME=,{"CA"}>}Gross)
• sum({<PERIOD_YEAR=,PERIOD ={"=$(=date(MonthStart(Date#(max(PERIOD)),-1),'YYYY.MM')))"},MONTHNAME=,MONTH_NAME={"OS"}>}Gross)

You do appear to have too many closing parentheses in the last two:

... -

sum({<PERIOD_YEAR=,PERIOD ={"=$(=date(MonthStart(Date#(max(PERIOD)),-1),'YYYY.MM'))"}, MONTHNAME=, MONTH_NAME=,{"CA"}>}Gross)

sum({<PERIOD_YEAR=,PERIOD ={"=$(=date(MonthStart(Date#(max(PERIOD)),-1),'YYYY.MM'))"},MONTHNAME=, MONTH_NAME={"OS"}>}Gross)

[Anonymous]

Hi

I have problem with last two set expression.

I am getting values for the above two set expressions.

I have proble with thses two set expressions.

sum({<PERIOD_YEAR=,PERIOD={"=$(=Date(addmonths(max(PERIOD),-1),'YYYY.MM'))"},MONTHNAME=,MONTH_NAME={"CA"}>}Gross)

/

sum({<PERIOD_YEAR=,PERIOD={"=$(=Date(addmonths(max(PERIOD),-1),'YYYY.MM'))"},MONTHNAME=,MONTH_NAME={"OS"}>}Gross)

Thanks

[sasiparupudi1 · Accepted solution]

try

(sum({<PERIOD_YEAR=,PERIOD={"$(=Date(addmonths(date#(max(PERIOD),'YYYY.MM'),-1),'YYYY.MM'))"},MONTHNAME=,MONTH_NAME={"CA"}>}Gross)/

(sum({<PERIOD_YEAR=,PERIOD={"$(=Date(addmonths(date#(max(PERIOD),'YYYY.MM'),-1),'YYYY.MM'))"},MONTHNAME=,MONTH_NAME={"OS"}>}Gross)

[Alvaro_Palacios]

Hi Rgv Rand,

You need to pass a date to the addmonths() function. So this should work out:

(sum({<PERIOD_YEAR=,PERIOD={"$(=Date(AddMonths(date(max(PERIOD)),-1),'YYYY.MM'))"},MONTHNAME=,MONTH_NAME={"CA"}>}Gross))

/

(sum({<PERIOD_YEAR=,PERIOD={"$(=Date(AddMonths(date(max(PERIOD)),-1),'YYYY.MM'))"},MONTHNAME=,MONTH_NAME={"OS"}>}Gross))

Regards,

Alvaro P.

[Anonymous]

Thank You