Latest data value

[absekrafi]

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

[poluvidyasagar]

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

or

you could write something like this.

Ex:

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

max(date) will be your latest date.

[absekrafi]

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

[poluvidyasagar]

Try this:

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

[poluvidyasagar]

Check the attachement

[absekrafi]

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

[jensmunnichs]

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

[absekrafi]

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

[jensmunnichs]

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