Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Not applicable

How to get the last value from a product in time

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

MonthProductQty
11234567810
212345678

-5

31234567810
4123456785

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

1 Solution

Accepted Solutions
kkkumar82
Valued Contributor III

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

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

View solution in original post

13 Replies
kkkumar82
Valued Contributor III

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

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

Try like this in the text box

Regards

Kiran kumar

Highlighted
O11235
New Contributor III

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

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" .

O11235
New Contributor III

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

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.

Digvijay_Singh
Honored Contributor III

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

Check this -

=FirstSortedValue(Qty,-Month)

Digvijay_Singh
Honored Contributor III

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

The last reply went to moderator, so trying again -

Check this -

=FirstSortedValue(Qty,-Month)

kkkumar82
Valued Contributor III

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

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


Try this


Kiran Kumar

kkkumar82
Valued Contributor III

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

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

Not applicable

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

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

Digvijay_Singh
Honored Contributor III

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

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.