topic Re: expression to find first and last date in QlikView

expression to find first and last date

omyahamburg — Thu, 14 Mar 2013 09:09:38 GMT

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

Re: expression to find first and last date

Gysbert_Wassenaar — Thu, 14 Mar 2013 09:15:21 GMT

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.

Re: expression to find first and last date

Gabriel — Thu, 14 Mar 2013 09:16:37 GMT

Hi,

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

Regards,

Gabrile

Re: expression to find first and last date

Thu, 14 Mar 2013 09:33:09 GMT

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

Re: expression to find first and last date

omyahamburg — Thu, 14 Mar 2013 10:15:10 GMT

Thanks Gysbert

My dates are numeric and it works perfect.

Re: expression to find first and last date

omyahamburg — Thu, 14 Mar 2013 14:01:07 GMT

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 ?

Re: expression to find first and last date

Gysbert_Wassenaar — Thu, 14 Mar 2013 14:47:12 GMT

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.

Re: expression to find first and last date

omyahamburg — Thu, 14 Mar 2013 14:51:16 GMT

Thanks a lot Gysbert.

Re: expression to find first and last date

maahivee — Thu, 27 Aug 2015 16:37:03 GMT

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.