Obtain data from MIN / MAX dates

[drew61199]

I have a dataset of vehicle utilization. I want to show monthly utilization, so need the "begin mileage" from start of month and "ending mileage" from end of month. Logically you'd think MIN(begin_mileage) and MAX(end_mileage) would work, but due to input errors, we may have a vehicle that starts at 100,000, ends at 110,000, but someone erroneously inputs 10,000 mid month on a random day. So I specifically need to focus on first/last day of the month (YYYY-MM-DD format).  I'm new to QLIK and have no idea how to write this expression. Thanks in advance! 

[HugoRomeira_PT]

I propose you use set analysis to find the values at the Min(Date) and Max(Date), this way the expression fits to and aggregation per Month, Year, Quarter, etc.

Tou just need to replace the 'yourDate' by your date field.

MIN({<yourDate={'=$(=min(yourDate))'}>}begin_mileage)

MAX({<Date={'=$(=Max(Date))'}>}begin_mileage)

Hope it helps!

[vinieme12]

Mileage on Month start

MAX({<Datefield={"$(=Date(Monthstart(Max(Datefield)))"}>}begin_mileage)

Mileage on Month end/latest date

MAX({<Datefield={"$(=Date(Max(Datefield))"}>}end_mileage)

But the problem of doing this in set analysis is, you won't be able to create a chart with Month/ Quarter as a dimension to see monthly summary of start and end mileage

As set analysis is evaluated once for the chart and not each row 

So if you need to do a monthly summary 

Then use if() an block as below 

Monthstart Milegage

=Max(aggr(If(Datefield=Min(Datefield) ,begin_mileage),Datefield,Month)

MonthEnd Milegage

=Max(aggr(If(Datefield=Max(Datefield) ,end_mileage),Datefield,Month)

[drew61199]

I appreciate both of your replies. Unfortunately Hugo's does not appear to be working. It appears to be returning the minimum value same as MIN(begin_mileage). I don't know if it's b/c some vehicles don't have utilization on May 1 or another issue, but as a specific example:

a sample vehicle has 560,770 on May2 (first day used of month), input error on 5/11 of 56,357, and final mileage of 561,179 on May 31. The query is returning the 56,357 entry. 

I'm trying another one:

=FirstSortedValue(DISTINCT(begin_mileage),end_date) but getting some NULL values (not many though). I believe they're due to actual NULLS in the dataset, but am currently validating. Does anyone see a reason this expression would not work? 

I'm also going to plug in Vinieme's to test / compare as well. Thanks again to all