3 Replies Latest reply: May 18, 2017 11:22 AM by Randall Lau RSS

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

    Randall Lau



      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_DateVehicle_NoBattery Voltage
      5/17/2017 1:00155
      5/17/2017 1:00256
      5/17/2017 1:00357
      5/17/2017 1:00158
      5/17/2017 1:00259
      5/17/2017 1:00360
      5/16/2017 1:00161
      5/15/2017 1:00262
      5/14/2017 1:00363



        Just using Min(Occur_Date) nets me:

      5/16/2017 1:00:005/15/2017 1:00:005/14/2017 1:00:00



      The closest I've come to success...

      Qlik Chart.JPG













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















      //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))

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

          Maybe :

          As dimensions:


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


          as a measure: sum(Battery_Voltage):





          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])




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

            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:


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



            Correct output:

            BatteryVoltTest Table Correct.JPG



            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



            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