2 Replies Latest reply: Dec 15, 2014 8:22 AM by Myles Holland RSS

    Attempting to make a Chart more robust/pivot data

    Myles Holland
      Hello all,
      I have an idea that I would like to explore, but can't really nail down how to approach my script for this addition I would like to make.
      To explain I have a bar chart that I created to help identify on today's date what project milestones were acheived yesterday, and what projects milestones were missed. I would like to create button(s) (maybe a slider) that will let me change what today is. I believe it is imporant to note that yesterdays date is based on the 'Milestone.Revised Date' field.
      Within this chart I have a several expressions.
      Achievement:
      =count(
      {<
      [Project Details.Load Date]={'*'}
      ,
      [TD.DateWeekEnd]={"$(=WeekEnd([Project Details.Load Date]))"}
      >}
      if( [Project.Load Date]=[Project Details.Load Date]
      AND NOT [Project Status]='PROJECT CANCELLED' AND NOT [Project Status]='ON HOLD' AND NOT [Project Status]='PROJECT AWARDED'
      AND Milestone.Flag = 0
      AND NOT IsNull([Milestone.Actual Date])
      AND [Milestone.Revised Date]=[Project Details.Load Date]
      , [Milestone]))

      Missed:

       

      count(
      {<
      [Project Details.Load Date]={'*'}
      ,
      [TD.DateWeekEnd]={"$(=WeekEnd([Project Details.Load Date]))"}
      >}
      if( [Milestone.Load Date]=[Project Details.Load Date]
      AND [Milestone.Revised Date]=[Project Details.Load Date]
      AND isNull([Milestone.Actual Date])
      AND NOT [Project Status]='PROJECT CANCELLED' AND NOT [Project Status]='ON HOLD' AND NOT [Project Status]='PROJECT AWARDED' AND NOT [Project Status]='COMPLETED'
      AND Milestone.Flag = 0
      ,
      [Project Number]))


      I then have another query that was develped by a colleague in which runs on a count if based query to identify x out of y:
      ='( '&
      count(
      {<
      [Project Details.Load Date]={'*'}
      ,
      [TD.DateWeekEnd]={"$(=WeekEnd([Project Details.Load Date]))"}
      >}
      if( [Milestone.Load Date]=[Project Details.Load Date]
      AND NOT [Project Status]='PROJECT CANCELLED' AND NOT [Project Status]='ON HOLD' AND NOT [Project Status]='PROJECT AWARDED'
      AND Milestone.Flag = 0
      AND NOT IsNull([Milestone.Actual Date])
      AND [Milestone.Revised Date]=[Project Details.Load Date]

      ,
      [Milestone]))
      &' out of '&
      (
      count(
      {<
      [Project Details.Load Date]={'*'}
      ,
      [TD.DateWeekEnd]={"$(=WeekEnd([Project Details.Load Date]))"}
      >}
      if( [Milestone.Load Date]=[Project Details.Load Date]
      AND NOT [Project Status]='PROJECT CANCELLED' AND NOT [Project Status]='ON HOLD' AND NOT [Project Status]='PROJECT AWARDED'
      AND Milestone.Flag = 0
      AND NOT IsNull([Milestone.Actual Date])
      AND [Milestone.Actual Date]<=[Milestone.Revised Date]
      AND [Milestone.Revised Date]=[Project Details.Load Date]
      AND WeekDay([Milestone.Revised Date]=TD.DateWeekday)

      ,
      [Milestone]))
      +
      count(
      {<
      [Project Details.Load Date]={'*'}
      ,
      [TD.DateWeekEnd]={"$(=WeekEnd([Project Details.Load Date]))"}
      >}
      if( [Milestone.Load Date]=[Project Details.Load Date]
      AND [Milestone.Revised Date]=[Project Details.Load Date]
      AND isNull([Milestone.Actual Date])
      AND NOT [Project Status]='PROJECT CANCELLED' AND NOT [Project Status]='ON HOLD' AND NOT [Project Status]='PROJECT AWARDED' AND NOT [Project Status]='COMPLETED'
      AND Milestone.Flag = 0
      ,
      [Milestone])))
      &' )'
      As always any help is greatly appreciated.
      Regards,
      Myles