Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Previous Month Value

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

21 Replies
Highlighted
MVP
MVP

Try removing few unnecessary quotes like:

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

Highlighted
Partner
Partner

Using this start giving - rather than 0

Highlighted
MVP
MVP

Are you selecting the same month field -STOCK_PERIOD?

Highlighted
Master III
Master III

Try to check your expression and date format

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

here ) is extra

Highlighted
Master III
Master III

Check this expression using max function in text box

try like this

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

Highlighted
Partner
Partner

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.

Highlighted
Partner
Partner

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

Highlighted
MVP
MVP

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

Highlighted
Master III
Master III

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