Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
can you post the screenshot for STOCK_PERIOD list box ? Is it date or string ?
It is string which i convert it into date and then successfully getting previous month name.
No there is no other selection. !
Issue is on second sheet in last column.
Could you please share some sample data..
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))
It looks working, Can you please explain in my expression i have to manually add '=' sign to compare but you did not use any.
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))
Because for <= type operators we have to provide the operator with single quotes.
>= 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.
Hi Zahid
please close the thread if you found your answer.