topic Re: 'last month selected' as a modifier in QlikView

'last month selected' as a modifier

Wed, 10 Jul 2013 04:42:01 GMT

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.

Re: 'last month selected' as a modifier

lironbaram — Wed, 10 Jul 2013 05:23:50 GMT

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

Re: 'last month selected' as a modifier

Thu, 11 Jul 2013 01:26:47 GMT

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.

Re: 'last month selected' as a modifier

tresB — Thu, 11 Jul 2013 04:38:44 GMT

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.

Re: 'last month selected' as a modifier

Thu, 11 Jul 2013 04:48:07 GMT

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!