topic Re: How to remove previous month stock amount ? in QlikView

How to remove previous month stock amount ?

paulyeo11 — Fri, 01 Aug 2014 07:20:43 GMT

How to remove my Previous month stock amount ?

Hi All

I have a Table which is working fine.

Due to Previous month data is not consider as D/O not release. So i need to remove Previous month amount.70.74 & 94.61.

Re: How to remove previous month stock amount ?

alexandros17 — Fri, 01 Aug 2014 07:23:14 GMT

Try with something like:

Sum({$ <mymonth={'$(>=(month(totay()))'}> }Amount)

Re: How to remove previous month stock amount ?

paulyeo11 — Fri, 01 Aug 2014 07:23:39 GMT

Enclosed my QV doc. which explan more in detail .

Re: How to remove previous month stock amount ?

paulyeo11 — Fri, 01 Aug 2014 07:38:41 GMT

Hi Ale

I try below code it return nul val :-

Sum({$ <Month={'$(>=(Month(Today()))-1'}> }[TOTAL_AMT])

Any alternative ?

Paul

Re: How to remove previous month stock amount ?

Anonymous — Fri, 01 Aug 2014 07:39:06 GMT

Hi,

Try below,

sum({$<month-={$(=Num(Month(AddMonths(Today(),-1))))}>} [TOTAL_AMT])/1000

Re: How to remove previous month stock amount ?

paulyeo11 — Fri, 01 Aug 2014 08:07:35 GMT

Hi Satyadev

Your script work fine. But when i use it in my actual QV doc , which is not working.

My actual QV doc is reading QVD file from the file i have attach here. So i think it is due to i cannot use AddMonths , it is possible just using SET command ? like Mr Ales approach.

Paul

Re: How to remove previous month stock amount ?

Anonymous — Fri, 01 Aug 2014 08:40:43 GMT

Hi Paul,

AddMonths is QlikView function, you can use it in your script. Basically I am subtracting one month from current month by using AddMonths function and comparing and removing it from the field called "month". Check if you have "month" column in your application.

You may not see past month data by using Ales script and if you are looking for same thing you can use Ales script. Remember, you should not use Month(Today())-1 as it will give you "0" when you are in Jan month and that's the reason I am using AddMonths(Today(), -1).

Re: How to remove previous month stock amount ?

paulyeo11 — Fri, 01 Aug 2014 09:28:26 GMT

Hi Saty

Thank you vey much your expression now working fine. ( This issue i encounter few time ) .

By the way thank you very much for your help.

it is possible to move one more step further , which is assist me modify the code , by using the SET analysis , so that user can click on year 2013 month Dec , so they are able to view the Amount till 2013 Dec. As now i can only remove the previous month amount. As my need is also remove 3 month ago. Hope you can help me.

Paul

Re: How to remove previous month stock amount ?

Anonymous — Fri, 01 Aug 2014 10:10:18 GMT

So basically you are looking for below three SETs to be merged together in single script?

1) Remove previous month data

Sum({$<month-={$(=Num(Month(AddMonths(Today(),-1))))}>} [TOTAL_AMT])

2) Calculate YTD base on year and month selection

Variable1 => vSelectedYear=Year(Max(Date))

Variable2 => vSelectedMonth=Month(max(Date))

Sum({<Year={$(vSelectedYear)},Month={'<=$(vSelectedMonth)'} >} TOTAL_AMT)

3) Remove last three months data

Sum({$<month-={$(>=Num(Month(AddMonths(Today(),-3)))<=Num(Month(AddMonths(Today(),-1))))}>} [TOTAL_AMT])

Re: How to remove previous month stock amount ?

paulyeo11 — Sat, 02 Aug 2014 03:57:27 GMT

Hi Saty

Thank you for your expression , i have tested it is not i need , in order to make thing simple , i start new post.

Paul