Qlik Sense Pivot Table Help: Determine a Field value based on another max(Field)

[#1]

Hello.  

I have a pivot table made using the following information.

What I am able to do in Qlik Sense is create the pivot and find the min date for each Vehicle_No. (Note: I used Excel to make this example of what I'm attempting to do).

Question:  How do I display the Battery Voltage in the box where the min date currently is, instead of the date? 

I am trying to find the battery voltage, per truck, per the latest date.  I thought AGGR was the right path

I have listed out the various Measures I've attempted (denoted by // at the start of the line).  The bottom attempt appeared to work, however my real set has five vehicles & two returned values and three returned '-'.

Any help would be greatly appreciated.  It is worth noting that I may be missing something basic with syntax.  I have been using tutorial videos, these forums, and the help.qlik site for guidance.  However, I haven't managed to resolve this item.

Best regards,

Randy Lau

Occur_Date	Vehicle_No	Battery Voltage
5/17/2017 1:00	1	55
5/17/2017 1:00	2	56
5/17/2017 1:00	3	57
5/17/2017 1:00	1	58
5/17/2017 1:00	2	59
5/17/2017 1:00	3	60
5/16/2017 1:00	1	61
5/15/2017 1:00	2	62
5/14/2017 1:00	3	63

  Just using Min(Occur_Date) nets me:

1	2	3
5/16/2017 1:00:00	5/15/2017 1:00:00	5/14/2017 1:00:00

The closest I've come to success...

//(Only({$<[OCCUR_DATE]={"$(=max([OCCUR_DATE]))"},,VEHICLE_NO=("VEHICLE_NO")>}[BATTERY_VOLTAGE]))

//Only({}BATTERY_VOLTAGE)

//Sum({$<CategoryName={$(vDefaultCat)}>}Sales)

//I want the latest (field) based on latest occur date.

//Only({$<[OCCUR_DATE.Calendar.Date]={$(=max([OCCUR_DATE.Calendar.Date]))}>}BATTERY_VOLTAGE)

//$(="vBatteryVoltage")

//Only({$<[OCCUR_DATE.Calendar.Date]=$(Max([OCCUR_DATE.Calendar.Date]))>}BATTERY_VOLTAGE)

//Count({$<FAULT_OBJID={"=Max(FAULT_OBJID)"}>}BATTERY_VOLTAGE)

//Aggr(NODISTINCT Only(BATTERY_VOLTAGE), [OCCUR_DATE.Calendar.Date])

//Sum(If(Aggr(NODISTINCT Min(Date), ID) = Date, Value))

Only(If(Aggr(NODISTINCT MAX([OCCUR_DATE.Calendar.Date]), VEHICLE_NO) = [OCCUR_DATE.Calendar.Date], BATTERY_VOLTAGE))

[OmarBenSalem]

Maybe :

As dimensions:

1)Vehicle_No

2)=aggr(min( Date),Vehicle_No)

as a measure: sum(Battery_Voltage):

Or

If you don't want to show the Date field:

as dimension: Vehicle_No

as a measure:

Sum({<Date={"=aggr(min(Date),Date,Vehicle_No)"}>}[Battery Voltage])

result:

[#3]

Thank you for the quick response.

I do think that is close, but I am trying to not SUM & while trying to replicate your example, I see ONLY gave me a '-' value if I swap SUM in your equation with ONLY.

The chart I'd like to get, but cannot is like this:

.	Vehicle_No
.	1	2	3
Min_Date	5/16/2017 1:00	5/15/2017 1:00	5/14/2017 1:00
Battery Voltage	61	62	63

I want to find the Min_Date & determine what the value of the Battery Voltage field is for the Min_Date.

[#4 · Accepted solution]

It appears I have solved it.

Root Cause:  Date format was left to Default and I should have updated that to reflect what I actually have.

A couple of other threads helped me identify this & once resolved, another thread held the equation I needed.

Date value in inline statement

Time format including AM/PM | Qlik Community

Sum Values Having Max Date | Qlik Community

The equation I needed is as follows:

AVG({<[Date]={"=aggr(MAX([Date]),Vehicle_No)"}>}Battery_Voltage)

- The average of the single MAX(Date) is one value.

Correct output:

My Load Script:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY h:mm:ss TT';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET FirstWeekDay=6;

SET BrokenWeeks=1;

SET ReferenceDay=0;

SET FirstMonthOfYear=1;

SET CollationLocale='en-US';

SET CreateSearchIndexOnReload=1;

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

Load Date(Date_Time) as Date, Vehicle_No, Battery_Voltage  Inline

[

Date_Time,Vehicle_No,Battery_Voltage

5/17/2017 1:00:00 AM, 1, 55

5/16/2017 2:00:00 PM, 2, 56

5/17/2017 3:00:00 AM, 3, 57

5/17/2017 4:00:00 PM, 1, 50

5/16/2017 5:00:00 AM, 2, 59

5/17/2017 6:00:00 PM, 3, 60

5/16/2017 7:00:00 AM, 1, 61

5/15/2017 8:00:00 PM, 2, 62

5/14/2017 12:00:00 PM, 3, 63

];