2 Replies Latest reply: May 12, 2016 8:40 AM by Sarosh Effendi RSS

    Expression for counting ongoing project

    Sarosh Effendi

           Hello,

           I need help with the following expression which will be used in a bar chart depicting ongoing projects count(project), however, in order      to provide a time filter I would like an expression to account for a project as long as it is active in any of the 6 milestones. For eg.

           Project X should only be counted once for 2011 and Project Y should be counted for 2012,2013 and 2014.

           Alternatively, I'm not sure if this would be achieved by a expression at the list box level?

       

           Thanks all.

       

           Sarosh

       

       

      ProjectMIlestone 1MIlestone 2MIlestone 3Milestone 4MIlestone 5Milestone 6
      X1/1/20112/1/20116/1/20111/1/20112/2/20113/1/2011
      Y1/1/20122/1/20126/1/20126/1/20136/1/2013
      6/1/2014
        • Re: Expression for counting ongoing project
          Stefan Wühl

          You could try something like this in your LOAD script:

           

          SET DateFormat = 'M/D/YYYY'; // or 'D/M/YYYY'??

           

          CROSSTABLE (Milestone, MilestoneDate)

          LOAD Project, [Milestone 1], [Milestone 2], [Milestone 3], [Milestone 4], [Milestone 5], [Milestone 6]

          FROM YourTableSource;

           

          Now you have transformed to three fields, Project, Milestone, MilestoneDate.

           

          Now eiter link you milestone dates to a master calendar, create additional date fields in your single table or use a straight table /listbox with a calculated dimension:

           

          =Year(MilestoneDate)

           

          and expression:

          =Count(DISTINCT Project)