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

    Expression for counting ongoing project


           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.





      ProjectMIlestone 1MIlestone 2MIlestone 3Milestone 4MIlestone 5Milestone 6
        • 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:




          and expression:

          =Count(DISTINCT Project)