2 Replies Latest reply on Jul 18, 2018 2:44 PM by Jason Ing

    Help needed with "group and calculate over time"...

    Jason Ing

      Hello,

       

      I need help with two issues.  I've tried sets, groups, calculations, etc. etc. but no success.

       

      I'm hoping someone can at least point me in the right direction.

       

      THANK YOU FOR ANY HELP!

       

      Jason

       

      (Please note the two charts below are Excel examples).

       

      FIRST PROBLEM: HOW TO CREATE "CHART A" USING ONLY MEASURES AND "NO DATE" DATA?

       

      The blue bars are total count (0 and above except null) of the "March_Score" and "April_Score" measures. The lines are counts of Above and Below 80.

       

      Each chart point is from a measure. I need to be able to create a bar and lines from these measures and then chart them by month on the x-axis. However, there is no reliable date data for the X axis.

       

      I've tried associating the occurrence of a number in the March_Score column with a date but haven't been successful.

       

      Chart A:

       

      SECOND PROBLEM: HOW TO CREATE "CHART B" USING ONLY DIMENSIONS WITH DATA "FRAGMENTED" OVER MULTIPLE DIMENSIONS?

       

      It might be helpful to reference the Source Data Table below to see the data structure.

       

      Goal: If a target date (i.e., March) occurs in any "DOS_#" column, then count "Pass" from its corresponding "Audit_#" column.

       

      The "Mar-18" blue bar in Chart B is determined by counting "Pass" from AUDIT_01 and AUDIT_02 columns if there is a March time stamp in either DOS_01 and DOS_02 columns.

       

      The problem is somehow combining the Audit columns and DOS columns into bars and lines and then graphing them over time on the x-axis.

       

      Chart B:

       

      Source Data Table: