1 2 Previous Next 26 Replies Latest reply on Dec 17, 2016 2:34 AM by Kashish Chauhan

# Calculate total number of active people at end of every quarter

Hi Tableau community,

I have a data source that contains the column called "Hire Date" which displays the hire date of a person. Using that and putting a count distinct on the ID, I was able to calculate the number of hires that were made every quarter. I also have a column called Active where 'Y' means the person is still active and 'N' means the person is not active anymore. I am trying to calculate the total number of active people at the end of every quarter.

I have attached the tableau workbook also(Version 9.3).

Best Regards,

Jesse

• ###### 1. Re: Calculate total number of active people at end of every quarter

I hope I understood you correctly. Just bring Active dimension to filter box and select Y only. Please see picture below:

• ###### 2. Re: Calculate total number of active people at end of every quarter

Hello Jesse,

You can check for the attached solution. This may help.

-Yogesh

1 of 1 people found this helpful
• ###### 3. Re: Calculate total number of active people at end of every quarter

Hi Jesse,

Is this what you want?

Thanks and Regards,

Kashish

• ###### 4. Re: Calculate total number of active people at end of every quarter

Thanks for the response!

I will try to explain below what I am looking for:

5 people were hired in the first quarter and all were active until the end of the quarter. That leaves us with 5 active people at the end of this quarter

6 people were hired in the second quarter and 2 people were terminated(1 was hired in the first quarter and other one was hired in the second quarter).
So active count = 4 people still active from 1st quarter + 5 people active from 2nd quarter = Total 9 people active at the end of this quarter.

And so on.

I hope I was able to explain it better

• ###### 5. Re: Calculate total number of active people at end of every quarter

Thanks for the response!

I will try to explain below what I am looking for:

5 people were hired in the first quarter and all were active until the end of the quarter. That leaves us with 5 active people at the end of this quarter

6 people were hired in the second quarter and 2 people were terminated(1 was hired in the first quarter and other one was hired in the second quarter).
So active count = 4 people still active from 1st quarter + 5 people active from 2nd quarter = Total 9 people active at the end of this quarter.

And so on.

I hope I was able to explain it better

• ###### 6. Re: Calculate total number of active people at end of every quarter

Thanks for the response!

I will try to explain below what I am looking for:

5 people were hired in the first quarter and all were active until the end of the quarter. That leaves us with 5 active people at the end of this quarter

6 people were hired in the second quarter and 2 people were terminated(1 was hired in the first quarter and other one was hired in the second quarter).
So active count = 4 people still active from 1st quarter + 5 people active from 2nd quarter = Total 9 people active at the end of this quarter.

And so on.

I hope I was able to explain it better

• ###### 7. Re: Calculate total number of active people at end of every quarter

So, right click COUNTD(ID) on Marks card and do following:

1 of 1 people found this helpful
• ###### 8. Re: Calculate total number of active people at end of every quarter

This worked like a charm on my sample data source but when I tried this in my actual data source it didn't work quite well. Thanks for the response though. This definitely helped me in one way or the other!

• ###### 9. Re: Calculate total number of active people at end of every quarter

Jesse,

If any of solution provided here answered your question, can you please mark as correct answer so that it is cleared from question board that needs to be answered as well as other users in future can benefit from it. If not, please feel free to ask for help. Thank you.

Sherzod

• ###### 10. Re: Calculate total number of active people at end of every quarter

Hi Jesse,

Although you have already closed this thread but I think the following is the right approach. And this was the desired result as per your requirement.

If you want I can go ahead and explain this.

Thanks and Regards,

Kashish

• ###### 11. Re: Calculate total number of active people at end of every quarter

Jesse

we are here to help you. If you can post your work or another sample that reflects your data structure i try to explain it where yo can be off and solution so that it will work. Thank you

• ###### 12. Re: Calculate total number of active people at end of every quarter

Hi Jesse,

Just pivot the Hire date and Termination date fields. Then write a calculation:

If [date type]='Hire Date' then 1

elseif [date type]='Termination Date' then -1

end

Plot this calculation against your Pivoted Date field which contains the date values. Then filter out the null date values by dragging the date field to the filter shelf. Inside there exclude the null values.

Then do a running total of your calculation created above.

I guess this will even work with your data structure provided you pivot both the Date fields.

Thanks and Regards,

Kashish

• ###### 13. Re: Calculate total number of active people at end of every quarter

Hi Kashish,

Yes, if you can explain how you made that graph then it would be wonderful!!

Also, I am working on another issue and I was wondering if you could help me with that too.

I am trying to calculate the attrition rate which is basically:

Number of Terminations in a given quarter/Number of Hires in the same quarter * 100

Is it possible to achieve that without having to normalize the data source ? Just because I do need the original hire dates and termination dates for various visualization and action filters that I am working on. If its not possible then please let me know and I can share more insight on that with you.

Thanks and Best Regards,

Jesse.

Also Happy Friday!!

• ###### 14. Re: Calculate total number of active people at end of every quarter

Hi Sherzodbek,

I really appreciate all your help.

I am working on another issue which is related to this and I was wondering if you could help me with that.

I am trying to calculate the attrition rate which is basically:

Number of Terminations in a given quarter/Number of Hires in the same quarter * 100

Is it possible to achieve that without having to normalize the data source ? Just because I do need the original hire dates and termination dates for various visualization and action filters that I am working on. If its not possible then please let me know and I can share more insight on that with you.

Thanks and Best Regards,

Jesse.

Also Happy Friday!!

1 2 Previous Next