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

[paulyeo11]

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

[paulyeo11]

Can some one advise me .

[#3]

try this

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

see attached example aswell

[paulyeo11]

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

[MarcoARaymundo]

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

[paulyeo11]

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.

[MarcoARaymundo]

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.

[paulyeo11]

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