1 Reply Latest reply: Jul 2, 2015 2:26 PM by Marcus Sommer RSS

    Working on Limiting Bar Chart to only display x results

    Myles Holland

      Hello all,

       

      I'm working on a Bar Chart to display cycletime of various steps a process for certain areas of a business.

      Using the expression of:

      =num(

      if(IsNull(avg({$*<CycleType={'Step 1'}, [Business Area]={'BUSINESS 1'}, [Project.Flag]={'0'}, [Cycle Status]={'Actual'}>} CycleTime)), 0, avg({$*<CycleType={'Step 1'}, [Project.Flag]={'0'}, [Cycle Status]={'Actual'}>} CycleTime)) +
      if(IsNull(avg({$*<CycleType={'Step 2'}, [Business Area]={'BUSINESS 1'}, [Project.Flag]={'0'}, [Cycle Status]={'Actual'}>} CycleTime)), 0, avg({$*<CycleType={'Step 2'}, [Project.Flag]={'0'}, [Cycle Status]={'Actual'}>} CycleTime)) +
      if(IsNull(avg({$*<CycleType={'Step 3'}, [Business Area]={'BUSINESS 1'}, [Project.Flag]={'0'}, [Cycle Status]={'Actual'}>} CycleTime)), 0, avg({$*<CycleType={'Step 3'}, [Project.Flag]={'0'}, [Cycle Status]={'Actual'}>} CycleTime)) +
      if(IsNull(avg({$*<CycleType={'Step 4'},[Business Area]={'BUSINESS 1'}, [Project.Flag]={'0'}, [Cycle Status]={'Actual'}>} CycleTime)), 0, avg({$*<CycleType={'Step 4'}, [Project.Flag]={'0'}, [Cycle Status]={'Actual'}>} CycleTime)), '#,##0')


      I do have a MonthEnd Dimension as well as a YearMonth Dimension I can use to help limit the results. As of right now this displays months prior to January 2015 - June/July 2015 (which is all I want displayed).


      Regards,


      Myles !

        • Re: Working on Limiting Bar Chart to only display x results
          Marcus Sommer

          It's not quite clear what do you want to do - do you want limit the possible dimensions-values? If yes you could set such options within the tab presentations.

           

          And your expression it's unnecessary long and complicated, try something like this:

           

          variable: eExp

          avg({$*<CycleType={'Step $1'}, [Business Area]={'BUSINESS 1'}, [Project.Flag]={'0'}, [Cycle Status]={'Actual'}>} CycleTime)

           

          expression:

          rangesum($(eExp(1)), $(eExp(2)), $(eExp(3)), $(eExp(4)))

           

          Explanation: if you used the same and/or a quite similar piece of code/expression you could use variables for them - especially the feature of parametrize them is very powerful. Further by using from a range-function you didn't need any kind of NULL check, for each non numeric part will be 0 returned.

           

          - Marcus