Previous Month Value

[zahidrahim_ocp]

Dear Experts,

I have a period filter with JAN-17-----DEC-17 periods. I want if user select DEC-17 I can show him previous month figures as well. For this purpose i have obtained previous month using:

Upper(date(ADDMONTHS(date#((STOCK_PERIOD),'MMM-YY'),-1),'MMM-YY')))

Now i want to write a set expression to get the value but i am keep getting 0

Sum({<STOCK_PERIOD = {"'$(='=' & Upper(date(ADDMONTHS(date#((STOCK_PERIOD),'MMM-YY'),-1),'MMM-YY')))'"}>} (PRIMARY_QUANTITY*CMPNT_COST))

Please help.

Regards,

Zahid Rahim

[pradosh_thakur]

can you post the screenshot for STOCK_PERIOD list box ? Is it date or string ?

[zahidrahim_ocp]

It is string which i convert it into date and then successfully getting previous month name.

[zahidrahim_ocp]

No there is no other selection. !

Issue is on second sheet in last column.

[prma7799]

Could you please share some sample data..

[pradosh_thakur]

you should convert that as a date in the script for better use of the field.

try this for now

Sum({<STOCK_PERIOD = { "$(=text(Upper(date(ADDMONTHS(date#(STOCK_PERIOD,'MMM-YY'),-1),'MMM-YY'))))"}>} (PRIMARY_QUANTITY*CMPNT_COST))

or

Sum({<STOCK_PERIOD = { "$(=text(Upper(date#(ADDMONTHS(date#(STOCK_PERIOD,'MMM-YY'),-1),'MMM-YY'))))"}>} (PRIMARY_QUANTITY*CMPNT_COST))

[zahidrahim_ocp]

It looks working, Can you please explain in my expression i have to manually add '=' sign to compare but you did not use any.

[pradosh_thakur]

why do you need '=' to compare.

try this. if it is  not working then  use the expression i provided. But i would suggest you to change the STOCK_PERIOD to date format in the string

Sum({<STOCK_PERIOD = {"'$(=text( Upper(date(ADDMONTHS(date#(STOCK_PERIOD,'MMM-YY'),-1),'MMM-YY'))))'"}>} (PRIMARY_QUANTITY*CMPNT_COST))

[zahidrahim_ocp]

Because for <= type operators we have to provide the operator with single quotes.

[pradosh_thakur]

>= and <= Will not work in your case as STOCK_PERIOD is a string .That is the reason i asked you to change it to DATE . then you don't have to use the text () function.

[pradosh_thakur]

Hi Zahid

please close the thread if you found your answer.