topic Re: Latest data value in QlikView

Latest data value

absekrafi — Wed, 25 Nov 2020 16:16:04 GMT

Dear all,

I have a dimension called stock in my oracle table.

When selecting an interval of dates I want to display the stock of latest date only.

How to implement that in Qlikview?

Best regards,

Abdallah

Re: Latest data value

poluvidyasagar — Thu, 18 Oct 2018 13:54:31 GMT

You can define variables on the dates and form logic around it.

you could write something like this.

Ex:

= if(date= max(date), Stock, no)

max(date) will be your latest date.

Re: Latest data value

absekrafi — Thu, 18 Oct 2018 15:29:37 GMT

Hi Vidya,

I did this but it is not working:

= if({<TRANSACTION_DATE= {"=$(vEndDate)"}> } ,STOCK,-1)

Where vEndDate = Max(TRANSACTION_DATE).

Any help on this?

Thank you in advance

Abdallah

Re: Latest data value

poluvidyasagar — Thu, 18 Oct 2018 15:47:15 GMT

Try this:

if(TRANSACTION_DATE='$(vEndDate)',Stock,-1)

Re: Latest data value

poluvidyasagar — Thu, 18 Oct 2018 15:48:18 GMT

Check the attachement

Re: Latest data value

absekrafi — Fri, 19 Oct 2018 09:24:56 GMT

Hi Vidya,

If clause can not be the answer to the problem because I want "only and all" the STOCK field values of the end date to be displayed (selected).

Do you know how to use where clause in a straight table?

Thank you

Re: Latest data value

jensmunnichs — Fri, 19 Oct 2018 09:46:33 GMT

So if I understand correctly, you have multiple records for the same date and you don't want them to be grouped together in the table, right? So say you have 3 records for a certain day, and that day is the max(date) for your selection, you want your table to show all 3 records?

If that's the case, you'd need some sort of unique identifier for every record in your data. If you don't already have one, you can simply add something like 'RowNo() as [ID]' to your load statement to give every record its own number.

Next create a straight table, and use Vidya's expression. To hide all the rows that aren't the max(date), change the '-1' to '0' in the expression and make sure the 'suppress zero values' is checked for the expression in the presentation tab in the chart properties. As your dimension you want both the unique identifier (ID in this case) and transaction_date. Finally, to hide the ID if you don't need/want it, also in the presentation tab, select 'hide column' for the ID column.

The result can be seen in the attached file. Hope this is what you're looking for

Re: Latest data value

absekrafi — Fri, 19 Oct 2018 10:32:21 GMT

Hi Jens, Hi Vidya,

I have a field called TRANSACTION_DATE (date as type) and a field called stock. For every TRANSACTION_DATE I have a stock value.

I added vStarDate (=Min(TRANSACTION_DATE) ) and vEndDate(=Max(TRANSACTION_DATE) ).

I want to display the stock of the date vEndDate because its value reflects the stock available at the selected date (or the selected interval) and adding these values (of stock) has no meaning. The stock field represents the quantity of a product at a given date (again the sum of these values does not give a correct information).

So, I want to display only the stock of the vEndDate (the last date of the interval [vStartDate .. vEndDate])

Thank you in advance,

Abdallah

Re: Latest data value

jensmunnichs — Fri, 19 Oct 2018 10:39:53 GMT

Thanks for the clarification.

In that case, I'm assuming you only have one record for each date, so ignore the whole thing about adding an ID. In that case you just have to use Vidya's if expression but again, replace '-1' with '0' and make sure 'suppress zero values' is checked in the presentation tab. Use Transaction date as dimension, and you should have what you want. Watch what happens when you select a range of dates, it will still only show the stock for the most recent date selected (see attachment).