6 Replies Latest reply: Aug 3, 2018 3:37 PM by Justin Dallas RSS

    Use Aggr in Bar Chart without matching Dim

    Justin Dallas

      Hello Folks,

       

      I'm working on what I thought was a simple problem.  Long story short, I don't want to double count values, so I'm using some monstrosity of

       

      SUM(AGGR(SUM(CarFuelAmountGal), CarId))
      

       

      In a bar chart using a Drilldown dimension of (Year, Month, Week).  Of course, this is wrong and I think since the dimension isn't matching up.

       

      Demo Data - Let's assume we have a trip list, with each row containing a Car Id, the fuel in the car and a name of a person in the vehicle. 

       

      TripTable:
      LOAD *,
      DayStart(Date([Trip Date Text])) AS '%trip_cal_key',
      Date([Trip Date Text]) AS [Trip Date]
      ;
      LOAD * Inline
      [
      'CarId', 'CarFuelAmountGal', 'CarTripParticipant', 'Participant Number' ,'Trip Date Text'
          1, 50, 'Jimi Hendrix', 'Driver', 8/13/2018
          1, 50, 'Janis Joplin', 'Passenger', 8/13/2018
          
          2, 35, 'Eric Clapton', 'Driver',10/12/2017
          2, 35, 'The Beegees', 'Passenger', 08/12/2017
          
          3, 27, 'Stevie Wonder', 'Driver',05/09/2016
          3, 27, 'Rush', 'Passenger', 05/09/2016
      ]
      ;
      

       

       

      Now imagine that I want to see the amount of fuel in each car as my measure, with a Calendar drilldown as my dimension.  In SQL I would say "SELECT SUM(CarFuelAmountGal) From SomeTable GROUP BY CarId".  This isn't quite working in my QlikSense barchart and it's putting the measure wherever it feels like.

       

      I've attached the demo to this post to better show the problem I'm trying to solve.

       

      Any help is greatly appreciated.