1 Reply Latest reply on Jul 9, 2018 7:14 PM by swaroop.gantela

    Make a plot showing a "total" and a "sum of a subset of components" (over time) where sum is less than the total i.e., there are "missing data".

    Trevor King

      The Dashboard in the attached .tbwx explains this question.

       

      I have data for oil consumption in Europe (over time), for individual countries and for "Total Europe". The problem is that the sum for all component countries is less than "Total Europe" i.e., there must be a few other countries missing. How do I make a single plot that combines an area plot -- like (3) in the attached -- with a line above it showing the total -- like (4) in the attached -- such that the white space in between indicates the other missing countries.

       

      Additionally, am I able to calculate this difference and assign it to "Other Countries".

        • 1. Re: Make a plot showing a "total" and a "sum of a subset of components" (over time) where sum is less than the total i.e., there are "missing data".
          swaroop.gantela

          Trevor,

           

          Please see if the workbook attached in the Forum Thread could give ideas.

           

          This approach takes a few steps.

           

          The Total Europe Value per Year is:

           

          {FIXED [Year]: SUM ( IF [Country]="Total Europe" THEN [Oilcons Mt] END ) }

           

           

          Similarly, the Total of all the Countries (minus the country called "Total Europe" is:

          {FIXED [Year]: SUM ( IF [Country]<>"Total Europe" THEN [Oilcons Mt] END ) }

           

          So the missing difference per year is the difference between the above two:

          [Total Europe]-[Country Total]

           

          To add this to the Area Graph, I created an new plot value:

          IF NOT(ISNULL(ATTR([Region]))) THEN SUM([Oilcons Mt])

          ELSE SUM([Missing Difference])

          END

           

          I removed the Region filter that was there on the Area Graph

          and aliased the Country of "Total Europe" to "z Missing Countries"


          I'm not sure if these manipulations will affect your other graphs.

           

          275153missing.png