topic Re: Previous Month Value in QlikView

Previous Month Value

zahidrahim_ocp — Fri, 29 Dec 2017 10:38:23 GMT

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

Re: Previous Month Value

tresB — Fri, 29 Dec 2017 10:43:08 GMT

Try removing few unnecessary quotes like:

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

Re: Previous Month Value

zahidrahim_ocp — Fri, 29 Dec 2017 10:46:17 GMT

Using this start giving - rather than 0

Re: Previous Month Value

tresB — Fri, 29 Dec 2017 10:48:37 GMT

Are you selecting the same month field -STOCK_PERIOD?

Re: Previous Month Value

prma7799 — Fri, 29 Dec 2017 10:50:09 GMT

Try to check your expression and date format

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

here ) is extra

Re: Previous Month Value

prma7799 — Fri, 29 Dec 2017 10:51:37 GMT

Check this expression using max function in text box

try like this

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

Re: Previous Month Value

zahidrahim_ocp — Fri, 29 Dec 2017 10:54:09 GMT

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.

Re: Previous Month Value

zahidrahim_ocp — Fri, 29 Dec 2017 10:58:19 GMT

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

Re: Previous Month Value

tresB — Fri, 29 Dec 2017 11:00:11 GMT

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

Re: Previous Month Value

prma7799 — Fri, 29 Dec 2017 11:03:42 GMT

Try this

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

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

Re: Previous Month Value

pradosh_thakur — Fri, 29 Dec 2017 11:08:05 GMT

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

Re: Previous Month Value

zahidrahim_ocp — Fri, 29 Dec 2017 11:11:17 GMT

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

Re: Previous Month Value

zahidrahim_ocp — Fri, 29 Dec 2017 11:13:57 GMT

No there is no other selection. !

Issue is on second sheet in last column.

Re: Previous Month Value

prma7799 — Fri, 29 Dec 2017 11:14:49 GMT

Could you please share some sample data..

Re: Previous Month Value

pradosh_thakur — Fri, 29 Dec 2017 11:15:02 GMT

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

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

Re: Previous Month Value

zahidrahim_ocp — Fri, 29 Dec 2017 12:52:52 GMT

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

Re: Previous Month Value

pradosh_thakur — Fri, 29 Dec 2017 13:01:19 GMT

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

Re: Previous Month Value

zahidrahim_ocp — Fri, 29 Dec 2017 13:03:57 GMT

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

Re: Previous Month Value

pradosh_thakur — Fri, 29 Dec 2017 13:12:34 GMT

>= 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.

Re: Previous Month Value

pradosh_thakur — Fri, 29 Dec 2017 13:57:20 GMT

Hi Zahid

please close the thread if you found your answer.