5 Replies Latest reply: Nov 18, 2015 3:43 PM by Sinan Ozdemir RSS

    Trying to compare with Previous Month

    Myles Holland

      Hello all,

       

      I have an expression set up that lets me average the amount of days it takes to complete a project. Within the dashboard I have a Multibox set up that allows the use to select the month they would like to view, which filters the below to only displayed completed stages within that period of time.

       

      This is expressed as a bar graph, and ideally I would like to create a new expression that looks the previous month than what is currently selected.

       

       

      =num(

      if(IsNull(avg({$*<Projectstage={'Stage1'}, [Project Status]-={'PROJECT CANCELLED', 'ON HOLD'}, [Project.flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)),
      0, avg({$*<Projectstage={'Stage1'},
      [Project Status]-={'PROJECT CANCELLED', 'ON HOLD'}, [Project.flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)) +


      if(IsNull(avg({$*<Projectstage={'Stage2'},
      [Project Status]-={'PROJECT CANCELLED', 'ON HOLD'}, [Project.flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)),
      0, avg({$*<Projectstage={'Stage2'},
      [Project Status]-={'PROJECT CANCELLED', 'ON HOLD'}, [Project.flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)) +


      if(IsNull(avg({$*<Projectstage={'Stage3'},
      [Project Status]-={'PROJECT CANCELLED', 'ON HOLD'}, [Project.flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)),
      0, avg({$*<Projectstage={'Stage3'},
      [Project Status]-={'PROJECT CANCELLED', 'ON HOLD'}, [Project.flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)) +


      if(IsNull(avg({$*<Projectstage={'Stage4'},
      [Project Status]-={'PROJECT CANCELLED', 'ON HOLD'}, [Project.flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)),
      0, avg({$*<Projectstage={'Stage4'},
      [Project Status]-={'PROJECT CANCELLED', 'ON HOLD'}, [Project.flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime))
      , '#,##0.0')

        • Re: Trying to compare with Previous Month
          Sinan Ozdemir

          I guess in your set analysis you can include one more condition if you have a date dimension already. In the below expression, I am also assuming that you have months in your date dimension such as Aug, but formatted as date:

           

          <[Date Dimension] = {$(=Date(AddMonths(Today(), -1), 'MMM'))}, ............>

          • Re: Trying to compare with Previous Month
            Myles Holland

            Hi Sinan,

             

            Thank you for your reply.

            I've seen the above syntax applied elsewhere, but not certain how to layer into my definition.

             

            You are correct in your assumption of my Date dimension, with one additional criteria being MMM YYYY. The dimension name is MonthName.

              • Re: Trying to compare with Previous Month
                Sinan Ozdemir

                Let me give a try:

                 

                =num(

                if(IsNull(avg({$*<MonthName = {$(=Date(AddMonths(Today(), -1), 'MMM YYYY'))}, Projectstage={'Stage1'}, [Project Status]-={'PROJECT CANCELLED', 'ON HOLD'}, [Project.flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)),
                0, avg({$*<MonthName = {$(=Date(AddMonths(Today(), -1), 'MMM YYYY'))}, Projectstage={'Stage1'},
                [Project Status]-={'PROJECT CANCELLED', 'ON HOLD'},[Project.flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)) +


                if(IsNull(avg({$*<MonthName = {$(=Date(AddMonths(Today(), -1), 'MMM YYYY'))}, Projectstage={'Stage2'},
                [Project Status]-={'PROJECT CANCELLED', 'ON HOLD'}, [Project.flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)),
                0, avg({$*<MonthName = {$(=Date(AddMonths(Today(), -1), 'MMM YYYY'))}, Projectstage={'Stage2'},
                [Project Status]-={'PROJECT CANCELLED', 'ON HOLD'},[Project.flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)) +


                if(IsNull(avg({$*<MonthName = {$(=Date(AddMonths(Today(), -1), 'MMM YYYY'))}, Projectstage={'Stage3'},
                [Project Status]-={'PROJECT CANCELLED', 'ON HOLD'}, [Project.flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)),
                0, avg({$*<MonthName = {$(=Date(AddMonths(Today(), -1), 'MMM YYYY'))}, Projectstage={'Stage3'},
                [Project Status]-={'PROJECT CANCELLED', 'ON HOLD'},[Project.flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)) +


                if(IsNull(avg({$*<MonthName = {$(=Date(AddMonths(Today(), -1), 'MMM YYYY'))}, Projectstage={'Stage4'},
                [Project Status]-={'PROJECT CANCELLED', 'ON HOLD'}, [Project.flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)),
                0, avg({$*<MonthName = {$(=Date(AddMonths(Today(), -1), 'MMM YYYY'))}, Projectstage={'Stage4'},
                [Project Status]-={'PROJECT CANCELLED', 'ON HOLD'},[Project.flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime))
                , '#,##0.0')


                Hope this works