topic Re: Compute stock amount by SET expression allow user select any date. in QlikView

Compute stock amount by SET expression allow user select any date.

paulyeo11 — Wed, 10 Apr 2013 08:36:12 GMT

Hi All

The Feb 2013 stock amount = 12 , Because i use sum(amount) that is 5 + -1 + 5 + -1 + 5 + -1=12, that is when user don't click on month and year. ( My data structure is consist of 2 column date and stock amount.

when user click on Feb = month , now it display 3 how to make it display stock amount = 12 ?

when user click on Jan = month , now it display 3 how to make it display stock amount = 8 ?

So i need help on SET expression to help me get dynamic stock amount depend on user selected month and year.

Hope some one can help me.

Enclosed my simple QV doc.

Paul

Re: Compute stock amount by SET expression allow user select any date.

paulyeo11 — Wed, 10 Apr 2013 22:32:13 GMT

Can some one advise me .

Re: Compute stock amount by SET expression allow user select any date.

Thu, 11 Apr 2013 10:43:08 GMT

try this

=Sum({$<date = {"<=$(=Max({$}[date]))"}, month = >}[Amount])

see attached example aswell

Re: Compute stock amount by SET expression allow user select any date.

paulyeo11 — Thu, 11 Apr 2013 19:04:06 GMT

Hi Sir

Many Thank for your reply.

Your expression work fine . But now when user also need to select year=2012 or 2013 ( as my actual data for month =1 have appear in 2012 and 2013 ) , it is possible add year field in your SET expression , so that it will still show the correct stock amount .

Paul

Re: Compute stock amount by SET expression allow user select any date.

MarcoARaymundo — Thu, 11 Apr 2013 20:05:47 GMT

Use this sum({$<stock_id-={'$(=Null())'}>} bal_qty)

Re: Compute stock amount by SET expression allow user select any date.

paulyeo11 — Thu, 11 Apr 2013 23:24:48 GMT

Hi Sir, Thank you veyr much for sharing.

Your script work a bit better , because when i select year =2013 it can display me the stock bal for 2013. but if i want to see jan stock balanace or feb stock bal , it cannot show me. enclosed is the QV doc i show you.

Re: Compute stock amount by SET expression allow user select any date.

MarcoARaymundo — Fri, 12 Apr 2013 11:54:51 GMT

OK! But, i tested and works fine.

Remenber, you have: jan/2012 and jan/2013. If you clear filters and select only month=1, appear both.

See attached file.

Sorry! My English is weak.

Re: Compute stock amount by SET expression allow user select any date.

paulyeo11 — Fri, 12 Apr 2013 17:17:43 GMT

Hi Sir

My english is also not very powerful , and that is why i unable to make you understand what i want. So in order to get what i want , i start new post , hope that the example i use are more easy to understand , in order i am able to get the expression.

I unable to give the link , as now all post need admin to aprove.

Paul