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

      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_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:

      123
      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

       

       

       

       

       

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

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

          Maybe :

          As dimensions:

          1)Vehicle_No

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

           

          as a measure: sum(Battery_Voltage):

          Capture.PNG

           

          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:

          Capture.PNG

          • 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:

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

            - 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

            [

            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

             

             

            ];