Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

omyahamburg
Contributor II

expression to find first and last date

Hello,

I have a table showing items, dates and quantities:

Unbenannt.png

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

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: expression to find first and last date

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
8 Replies
MVP & Luminary
MVP & Luminary

Re: expression to find first and last date

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
Gabriel
Valued Contributor III

Re: expression to find first and last date

Hi,

I would recommend using FISRTSORTVALUED() function and may be with MAX() & MIN() function.

Regards,

Gabrile

Not applicable

Re: expression to find first and last date

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');

omyahamburg
Contributor II

Re: expression to find first and last date

Thanks Gysbert

My dates are numeric and it works perfect.

omyahamburg
Contributor II

Re: expression to find first and last date

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 ?

MVP & Luminary
MVP & Luminary

Re: expression to find first and last date

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
omyahamburg
Contributor II

Re: expression to find first and last date

Thanks a lot Gysbert.

maahivee
New Contributor III

Re: expression to find first and last date

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.

Community Browser