-
1. Re: How to add an initial Count
Tom WMay 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.
-
HeadcountRolling.twbx 7.9 KB
-
-
2. Re: How to add an initial Count
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 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
-
RoulementV2.twbx 212.9 KB
-
-
4. Re: How to add an initial Count
Tom WMay 25, 2016 8:47 AM (in response to David Vincent)
1 of 1 people found this helpful- 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.
-
5. Re: How to add an initial Count
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 WMay 25, 2016 9:37 AM (in response to David Vincent)
1 of 1 people found this helpfulwrap 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.
-
7. Re: How to add an initial Count
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 WMay 25, 2016 3:48 PM (in response to David Vincent)
1 of 1 people found this helpfulWhen 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
-
9. Re: How to add an initial Count
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