1 Reply Latest reply on Jul 7, 2016 8:01 PM by swaroop.gantela

    Mutually exclusive 'if' statements in a bar graph

    Reesha Santhosh

      Hi all,

       

      I need to plot the number of active devices against fiscal quarters. A device could be active over multiple quarters and should be counted in all the quarters that it is active. The data is saved with only start dates and end dates (no field for quarter names) in the database. As this is fiscal quarters, I need calculated fields to determine what quarter the dates fall in.

       

      Example, quarter Q1 2016 could be

       

      IF [End Date] >= DATE("7/10/2015") AND [Start Date] <= DATE("10/20/2015")

      THEN 'Q1 2016'

      END

       

      And, Q2 2016 would be

       

      IF [End Date] >= DATE("10/21/2015") AND [Start Date] <= DATE("01/22/2016")

      THEN 'Q1 2016'

      END

       

      Similarly I have Q3 2016, Q4 2016, Q1 through Q4 2015 etc. Some sample data below.

       

      Per this data, device 1 should be counted as part of Q1 and Q2 2016

      device 2 - Q1 2016

      device 3 - Q1, Q2 and Q3 2016

      device 4 - None (or previous relevant quarters)

      device 5 - Q2 and Q3 2016

       

      I need to show this information in a bar graph. Please provide some insights on how to display number of active devices against fiscal quarters, where quarters are mutually exclusive ('columns' in the worksheet) and number of active devices should be double-counted over quarters. So, i basically have one measure (count of device id) for multiple dimensions (fiscal quarters) and would like it all in a bar graph.

       

      Device IDStart DateEnd Date
      12014-09-012016-02-25
      22015-01-252015-10-17
      32015-01-252016-01-24
      42014-09-012015-02-25
      52015-12-202016-02-25
        • 1. Re: Mutually exclusive 'if' statements in a bar graph
          swaroop.gantela

          Reesha,

           

          Please see if the attached could be a first step for you.

           

          I employed the techniques on kettan's excellent posts:

          CROSS JOIN with Tableau's join dialog

          The Cross Join Collection

           

          I made an attempt, but I may have overcomplicated things.

          The links above are I think the most important and clear parts.

           

          If feasible for your setup, you can create two more sheets,

          one listing all possible days, and another listing the start and end days of

          the quarters in question.

           

          Each sheet has a Key column of just the value 1 by which they can be joined.

          This creates all combinations of all devices, all dates, and all quarters.

          Then two filters are used:

          [Dates]>=[Start Date] AND [Dates]<=[End Date]

          to get just dates between start and end for device and

          [Dates]>=[Quarter Start] AND [Dates]<=[Quarter End]

          to get just the dates in the quarters

           

          I created a flag if a date was in a quarter,

          then per each device, I created an active flag if they had one

          date in a quarter using a Level of Detail calculation:

          { FIXED [Device ID]:MAX([InQuarter])}

           

          Finally, I got all the DeviceIDs that were active in a quarter.

          IF [ActiveInQuarter]=1 THEN [Device ID] END

           

          The plot was then of the QuarterStart date vs.  a distinct count of the Device IDs.

           

          210665q.png