

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
expression to find first and last date
Hello,
I have a table showing items, dates and quantities:
I want to determine for the item by an expression the first and the last date in the table.
Can someone let me know how ?
thx.
Joerg
- Tags:
- new_to_qlikview
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
max(total Date) for the maximum date and min(total Date) for the minimum date. But your dates will have to be numeric dates. For strings there are maxstring and minstring, but since the days are the first two characters those functions will not give you the result you want. If your dates are strings then you should make them real dates in the script using the date# function.
talk is cheap, supply exceeds demand

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
max(total Date) for the maximum date and min(total Date) for the minimum date. But your dates will have to be numeric dates. For strings there are maxstring and minstring, but since the days are the first two characters those functions will not give you the result you want. If your dates are strings then you should make them real dates in the script using the date# function.
talk is cheap, supply exceeds demand

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
I would recommend using FISRTSORTVALUED() function and may be with MAX() & MIN() function.
Regards,
Gabrile

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Another way of doing this if you want first and last row even if they are not the oldest and newest dates is
LET
rowsFechas = NoOfRows('Fechas');
let firstDate = peek('Fecha', 0, 'Fechas');
let lastDate = peek('Fecha', $(rowsFechas)-1, 'Fechas');


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks Gysbert
My dates are numeric and it works perfect.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Dear Gysbert
How can I count the number of months when (as in my case shown in the 1. post)
the min date is in previous year ?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You can find an expression to calculate the difference in months between two dates here. You can create the variable in the script with the SET keyword or manually add a variable with the expression as its value. Use it like =$(MonthDiff(Date1, Date2)) with Date1 and Date2 as the dates you want the difference between.
talk is cheap, supply exceeds demand


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks a lot Gysbert.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello Everyone- In the same requirement i want to find out the 1st po date and 1st po Qty
to get the 1st po date i am doing min(po date) which is working fine
but to get the 1st po qty i am trying to do
=Alt(FirstSortedValue(Aggr(Sum([Qty On Order]), [Item Number], [Promise Date]), [Promise Date]), Aggr(Sum([Qty On Order]), [Item Number], [Promise Date]))
=Sum({<[Promise Date] = {"$(=Min([Promise Date]))"}>} [Qty On Order])
Both the expressions are not working for me please give me some suggetions how to get the 1st PO Qty for that item.
the other thing happening =Sum({<[Promise Date] = {"$(=Min([Promise Date]))"}>} [Qty On Order]) with this expression is that it is working only if i select one item, and is displaying all over when no item is selected in the chart.
