Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
NEW webinar Dec. 7th: 2023 Outlook, A Pivotal Year for Data Integration SIGN ME UP!
cancel
Showing results for 
Search instead for 
Did you mean: 
drew61199
Contributor
Contributor

Obtain data from MIN / MAX dates

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! 

Labels (4)
3 Replies
HugoRomeira_PT
Creator
Creator

 

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!

If the issue is solved please mark the answer with Accept as Solution.
If you want to go quickly, go alone. If you want to go far, go together.
vinieme12
Champion III
Champion III

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)

 

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
drew61199
Contributor
Contributor
Author

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. 

drew61199_0-1654283264384.png

 

 

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