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!
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