Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Community,
I'm trying to retreive the 'Closing Stock' for a group of records. I only want to show the last closing stock (stored in a field [ST], which means that if I'm reporting on the enitre FY2013, then I only want to see the closing stock for the last month selected (in this case June).
How do I make this into a modifier??
Thanks for any help.
you have couple of options :
1.assuming all products have closing stock reports in last month
then you can use sum({<Month={"$(=max(Month))"}>}ST)
2. you can use firstsorted value , for this i will assume you have a date field in the table with the closing stock data
so you can use somthing like sum(firstsortedvalue(ST,-DateField))
hope it helps you
Hello liron,
Thank you very much for your answer... but unfortunately it doesn't seem to work.
Solution 1 didn't (surprisingly) and Solution 2 didn't think it would work because there is more than one figure for on category of products in any one month. firtsortedvalue() is the first thing I'd tried but apparently if more than one value is returned, than the answer is 0. Is that right?
Maybe I'm missing something in the script.
Closing Stock : I guess it has to be a single value for a date. If so, then using firstsortedvalue() sorted against date field would return single value. That should work.
Maybe I should aggregate in the script so to have a single value?
I'll do some testing and get back to you both. Thanks!