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

[tresesco]

Try removing few unnecessary quotes like:

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

[zahidrahim_ocp]

Using this start giving - rather than 0

[tresesco]

Are you selecting the same month field -STOCK_PERIOD?

[prma7799]

Try to check your expression and date format

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

here ) is extra

[prma7799]

Check this expression using max function in text box

try like this

AddMonths(max(date(date#([Posting Date],'DD/MM/YYYY'),'MMM-YY')),-1)

[zahidrahim_ocp]

Yes the filter is on STOCK_PERIOD field. which contains NOV-17, DEC-17 periods. I have checked the previous month giving me exactly previous month. suppose i select DEC-17

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

shows me NOV-17

Now the expression thing is the issue.

[zahidrahim_ocp]

I have tried below expression but giving -

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

[tresesco]

Is there any other selection you are making? Could you post a sample qvw?

[prma7799]

Try this

Upper(date(ADDMONTHS(max(date(date#(STOCK_PERIOD'DD/MM/YY'),'MMM-YY')),-1),'MMM-YY'))

or

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