expression to find first and last date

[omyahamburg]

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

[Gysbert_Wassenaar · Accepted solution]

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.

[Gabriel]

Hi,

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

Regards,

Gabrile

[#4]

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]

Thanks Gysbert

My dates are numeric and it works perfect.

[omyahamburg]

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 ?

[Gysbert_Wassenaar]

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.

[omyahamburg]

Thanks a lot Gysbert.

[maahivee]

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.