Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
omyahamburg
Creator II
Creator 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
Gysbert_Wassenaar

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

View solution in original post

8 Replies
Gysbert_Wassenaar

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
Partner - Specialist III
Partner - Specialist III

Hi,

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

Regards,

Gabrile

Not applicable

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
Creator II
Creator II
Author

Thanks Gysbert

My dates are numeric and it works perfect.

omyahamburg
Creator II
Creator II
Author

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.


talk is cheap, supply exceeds demand
omyahamburg
Creator II
Creator II
Author

Thanks a lot Gysbert.

maahivee
Contributor III
Contributor III

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.