9 Replies Latest reply on May 26, 2016 5:40 AM by David Vincent

    How to add an initial Count

    David Vincent
    David Vincent May 24, 2016 12:02 PM

      I would like to create some calculations beginning with my fiscal year. September is the first month of the year.  The initial Count of employees is 110.

       

      In september Got 7 hiring - 3 departures. At the end of the month i have 114 employees.  I need to start October with 114 employees then 7 hiring, 2 departures, end of month is 119 employees and so on for the whole year.  a preview of my data is attached

       

      Thanks

        • 1. Re: How to add an initial Count
          Tom W
          Tom W Ambassador May 24, 2016 12:43 PM (in response to David Vincent)

          If you cannot manipulate your source data, you will need to create a calculated field as the 'starting point'.

          I.e.; StartingPoint= IF [Month]= date('9/1/2015') then 110 else 0 end

          Effectively what this does is use your date/time dimension and only adds the 110 for that point in time.

           

          Then to calculate the headcount, create a calculation i.e. HeadCount =[StartingPoint] +[Hires]-[Departures]

           

          Add the headcount field to the report, enable a table calculation for 'Running Total' and you should be all set.

          1 of 1 people found this helpful
          • 2. Re: How to add an initial Count
            David Vincent
            David Vincent May 24, 2016 1:56 PM (in response to Tom W)

            Thanks for the quick answer and solution.  i Will play with my data, got some calculations issues, but i think it will really help me

             

            thanks again

            • 3. Re: How to add an initial Count
              David Vincent
              David Vincent May 25, 2016 8:29 AM (in response to Tom W)

              Finally, i'm not able to get the same result as you get, i modify my data to be able to play more with it, no success to get a successful initial count - when i get the value i want (100 for 2015) I'm not able to take this number for the headcount

               

              Any idea?

               

              (Mois = Month, Année Financière = Year)  Our year start in september

              • 4. Re: How to add an initial Count
                Tom W
                Tom W Ambassador May 25, 2016 8:47 AM (in response to David Vincent)
                • Take a look at your 'Mois' dimension values - you've setup the calculated field 'InitialCount' as IF [Mois]= date(09/01/2014) then 100 else 0 END , but your 'Mois' dimension does not contain a corresponding value of 09/01/2014

                 

                You need to anchor this to a value which exists i.e. 8/30/2014 or 9/27/2014

                • You need to enclose the date value in the 'InitialCount' calculated field in double quotes i.e. date("08/30/2014")

                 

                From that point on, you're missing some pretty critical pieces;

                • You need to create a Headcount field to calculate the initial count + the hires - the departures, as per my post above
                • You need to setup the Headcount field to use a Table Calculation for running total.
                1 of 1 people found this helpful
                • 5. Re: How to add an initial Count
                  David Vincent
                  David Vincent May 25, 2016 9:00 AM (in response to Tom W)

                  Thanks

                   

                  For that part i see my error - i tought it was taking the september month not a specific date and forgot the "":

                  You need to anchor this to a value which exists i.e. 8/30/2014 or 9/27/2014

                  • You need to enclose the date value in the 'InitialCount' calculated field in double quotes i.e. date("08/30/2014")

                   

                  For that part - Yes i create a new document for you to remove some information on the document and didn't recreate the headcount

                  From that point on, you're missing some pretty critical pieces;

                  • You need to create a Headcount field to calculate the initial count + the hires - the departures, as per my post above
                  • You need to setup the Headcount field to use a Table Calculation for running total.

                   

                  When i do the initalcount: september = 1200 because of the sum.  If i change it to ATTR (or MAX) it gaves me my 100 but not helping for the headcount calculation after that

                  • 6. Re: How to add an initial Count
                    Tom W
                    Tom W Ambassador May 25, 2016 9:37 AM (in response to David Vincent)

                    wrap your intialcount in a MIN;

                     

                    Headcount = MIN(InitialCount) + SUM(zn([Hires])) + SUM(ZN([Departures]))

                     

                    the zn function will replace the value with 0 if it's null - it's a good failsafe here.

                    1 of 1 people found this helpful
                    • 7. Re: How to add an initial Count
                      David Vincent
                      David Vincent May 25, 2016 10:30 AM (in response to Tom W)

                      Works very fine now  Thanks for all the tips.  If i can ask a last questions (i wish ;o)  Data looks good if i leave it in a month view, but if i change to a Year view, i lose my running total, same thing if i remove 2015 and only want to see 2016

                      • 8. Re: How to add an initial Count
                        Tom W
                        Tom W Ambassador May 25, 2016 3:48 PM (in response to David Vincent)

                        When you pull out the month level dimension, you lose the context necessary to apply the 'InitialCount' calculated field.

                         

                        You can get around it by changing the 'Headcount' calculation to be {FIXED [Mois]: MIN([InitialCount])+sum([Hiring]) - sum([Departure])}

                         

                        This is a Level of Detail expression (or LOD for short). They are awesome and you need to learn them! FIXED Level of Detail Expressions

                        1 of 1 people found this helpful
                        • 9. Re: How to add an initial Count
                          David Vincent
                          David Vincent May 26, 2016 5:40 AM (in response to Tom W)

                          I should be all set with those infos, so many things to learn =)

                           

                          thanks again for your time