3 Replies Latest reply: Jan 11, 2018 10:50 AM by Juraj Misina RSS

    help to calculate a expression

    sahil mahajan

      Hi need some help

       

      i want to calculate total no of hires :

       

      suppose i select nov and 2017

      i get the headcount for november and the attrition for november

       

      total hires will be = headcount for (nov -1) - attrition for nov + x = headcount for nov

       

      i want to find x please help me write it in a form of expression. will be a great help

      help req.png

        • Re: help to calculate a expression
          Juraj Misina

          Hello Sahil,

           

          you can achieve this by using set analysis (A Primer on Set Analysis). See a very simple example:

          Sum(Headcount) //headcount for selected period
          Sum({<Month={$(=Max(Month)-1)}>} Headcount) //headcount for previous month
          
          

          This example does not account for change of year (when you want to calculate your measure for January), so it needs to be expanded, but you should get the idea.

           

          Best

          Juraj

            • Re: help to calculate a expression
              sahil mahajan

              Hi Juraj thanks for the quick reply but still i am not able to get the desired result

               

              i used this expression

               

              count({<[As_On link.autoCalendar.Month]={$(=([As_On link.autoCalendar.Month])-1)}>} distinct EmployeeNo)

               

              but i am not getting the result pls help

               

              help 1.png

                • Re: help to calculate a expression
                  Juraj Misina

                  Hi Sahil,

                   

                  as I said, my example does not consider change in year, so hence the result. Try something like this:

                  count({<
                      [As_On link.autoCalendar.Year]={$(=Year(AddMonths(MakeDate(Max([As_On link.autoCalendar.Year]), Max([As_On link.autoCalendar.Month])), -1)))},
                      [As_On link.autoCalendar.Month]={$(=Month(AddMonths(MakeDate(Max([As_On link.autoCalendar.Year]), Max([As_On link.autoCalendar.Month])), -1)))}
                  >}
                  distinct EmployeeNo)
                  

                   

                  Again, this is just an example, although it should work in your case, but you should optimize it either by creating some sort of month sequence in your data (so you do not need to handle year when comparing Jan/Dec data across years), or by creating variables which would calculate correct year and month values for you, or by creating a The As-Of Table.

                   

                  Hope this helps

                  Juraj