topic Re: How to get the last value from a product in time in QlikView

How to get the last value from a product in time

Fri, 11 Mar 2016 11:22:25 GMT

Hello,

I am dealing with a problem for some time now and I can't find a solution that might be easy for you.

The problem seems simple but I am still a beginner in QlikView.

The situation is as follows:

I have a dataset with 4 months of data. In this period of time the quantity of products changed.

What I want is to get is the last quantity.

Imagine this, if I am looking for this last 4 months of data and the product 12345678

Month	Product	Qty
1	12345678	10
2	12345678	-5
3	12345678	10
4	12345678	5

I want to get the Qty = 5 for example in a TextBox.

I have tried the firstSortedValue but it is not returning the value I expected.

Please see attached file.

Thanks very much

Re: How to get the last value from a product in time

kkkumar82 — Fri, 11 Mar 2016 13:15:46 GMT

=Sum({<Month = {$(=max(Month))}>} Qty)

Try like this in the text box

Regards

Kiran kumar

Re: How to get the last value from a product in time

O11235 — Fri, 11 Mar 2016 13:30:28 GMT

I agree with kkkumar82.

"sum({<Month={"$(=max(Month))"}>}Qty)" works.

Do your self a favor have a look at Set Analysis: syntaxes, examples and get into "Set Analysis" .

Re: How to get the last value from a product in time

O11235 — Fri, 11 Mar 2016 13:37:17 GMT

When you feel a bit more adventurous this post by a good friend is also an option.

Rolling 3, 6 and 12 months calculation | Qlik Community‌

Good luck.

Re: How to get the last value from a product in time

Digvijay_Singh — Fri, 11 Mar 2016 13:43:13 GMT

Check this -

=FirstSortedValue(Qty,-Month)

Re: How to get the last value from a product in time

Digvijay_Singh — Fri, 11 Mar 2016 13:44:40 GMT

The last reply went to moderator, so trying again -

Check this -

=FirstSortedValue(Qty,-Month)

Re: How to get the last value from a product in time

kkkumar82 — Fri, 11 Mar 2016 13:49:56 GMT

=Sum({<Month = {$(=max(Month))}>} Qty)

Try this

Kiran Kumar

Re: How to get the last value from a product in time

kkkumar82 — Fri, 11 Mar 2016 14:05:11 GMT

Hi Vijay,

I think your expression won't work in a text box when there are multiple records, correct me If I am wrong.

Regards

Kiran Kumar

Re: How to get the last value from a product in time

Fri, 11 Mar 2016 14:56:14 GMT

Hello,

thanks for your reply. That works in fact, but I am now struggling with the date.

Because in my application the months are not a numeric like that in this example.

I have changed a little bit the example.qvw file. If you could see this issue in this way I would appreciate very much

Re: How to get the last value from a product in time

Digvijay_Singh — Fri, 11 Mar 2016 16:10:22 GMT

It works if you want just Quantity value from maximum month. If you need product wise Qty from latest month then use same expression in straight table with Product as dimension. See this sample created from your data.

Re: How to get the last value from a product in time

Fri, 11 Mar 2016 17:24:38 GMT

Hello,

Yes that works using the date as a Int like in this example. But in my application, Date is not like that.

The following error shows:

Error: Error in set modifier ad hoc element list: ',' or ')' expected

I've tried this but no luck:

=Sum({<num(Date)={$(=max(num(Date)))}>}#Qty)

The following error shows:

Error: Error in set modifier expression

Thanks

Re: How to get the last value from a product in time

kkkumar82 — Sat, 12 Mar 2016 01:55:12 GMT

Hi,

Are your dates in string format or in date format, if there are in date format

=Sum({<Date={'$(=max(Date))'}>}#Qty)

Thanks

Kiran Kumar

Re: How to get the last value from a product in time

Saravanan_Desingh — Sat, 12 Mar 2016 02:55:45 GMT

To get the Top Qty, you can also try like this..

Only({<Month={"=Rank(Month)=1"}>}Qty)

Re: How to get the last value from a product in time

Tue, 15 Mar 2016 11:22:33 GMT

Hello,

thanks a lot, that solved my problem.

Cheers