Question Re: SAC: Calculate number of headcount using a calculated measure in model in Technology Q&A

SAC: Calculate number of headcount using a calculated measure in model

former_member837245 — Tue, 21 Feb 2023 09:40:05 GMT

Dear SAC Expert,

Is it possible to create a calculated measure in the model to calculate the total number of headcount based on the active employee which has data populated in the model? Kindly advise.

Thank you.

Best Regards,

Yanice

Re: SAC: Calculate number of headcount using a calculated measure in model

N1kh1l — Tue, 21 Feb 2023 10:02:18 GMT

yanicewengyee

Can you put up an example of what kind of data you have in model and what you expect. If you have an attribute in employee which indicates if the employee is active/not, you can use it to derive a headcount based on that.

Nikhil

Re: SAC: Calculate number of headcount using a calculated measure in model

former_member837245 — Tue, 21 Feb 2023 10:13:12 GMT

Hi Nikhil,

Take below as an example. I would like to create a calculated measure (Eg. Number of Employee) which able to calculate the total number of employees from the same profit center & cost center. Is it doable to create such calculated measure in the model? The idea is once I pull the calculated measure "Number of Employee" into report, it should be able to show "2". Kindly advise.

Thank you.

Best Regards,

Yanice

Re: SAC: Calculate number of headcount using a calculated measure in model

N1kh1l — Tue, 21 Feb 2023 10:26:09 GMT

yanicewengyee

Not sure if got the image right. You second column looks like an employee dimension. If you just create a Aggregation measure of type count dimension on this column it should give you a headcount. Else you can go for calculated measure like below over your employee dimension. You might have to create another aggregation on top of this to have an exception aggregation over time so that headcounts do not get added up across time but get aggregated over other dimensions like cost center, profit center etc.

Example is based on customer count

IF(NOT(ISNULL([d/"SAP_CEP_SALES_PLANNING":SAP_CEP_CUSTOMER])) ,1 ,0 )

Aggregation Measure over above measure to get right aggregation

Output: Q1 count is not getting aggregated but count over customer hierrachy is getting aggregated.

Hope this helps !!

Nikhil

Re: SAC: Calculate number of headcount using a calculated measure in model

former_member837245 — Tue, 21 Feb 2023 11:04:15 GMT

Hi Nikhil,

Thanks a lot for the input. I think for our situation it's better to create a Aggregation measure of type count dimension in the model. We are actually building the report using SAP Analytics Cloud Microsoft Office Add-In, hence not able to create the calculated measure as suggested by you. Can you please advise if we able to create the aggregation measure in the model like below? I tried to create a calculated measure like below but it doesn't work.

Thank you.

Best Regards,

Yanice

Re: SAC: Calculate number of headcount using a calculated measure in model

N1kh1l — Tue, 21 Feb 2023 11:27:44 GMT

yanicewengyee

My previous example was from Account Model but I recreated an example from a Measure based model. Modelling an example based on orders as i do not have employees. Your formula looks ok but you will need one aggregated measure over it to get the right aggregation. Ad all dimensions over which you want to aggregate the head count on like CC, PC Comp code etc. The date aggregation will be taken as last from original measure. See an example below.

Order Count: Aggr Type Last will ensure I get correct numbers even on quarters and Years

Aggregated Order Count: To get correct aggregation over other dimensions

Even if you remove Order Id the aggregated measure will work correct but original measure will not show anything without it.

Hope this helps !!

Nikhil

Re: SAC: Calculate number of headcount using a calculated measure in model

N1kh1l — Tue, 21 Feb 2023 11:45:49 GMT

Also I am not sure of what all dimensions you have in your model. You can try setting the exception aggregation Headcount as Sum and in Exception aggregation dimension you can put all dimensions except time. That way you may not need second aggregation.

Nikhil

Re: SAC: Calculate number of headcount using a calculated measure in model

former_member837245 — Wed, 22 Feb 2023 10:24:44 GMT

Hi Nikhil,

Thank you so much for the idea and suggestion! We are able to achieve the outcome we want.

Best Regards,

Yanice

Re: SAC: Calculate number of headcount using a calculated measure in model

N1kh1l — Wed, 22 Feb 2023 10:30:14 GMT

yanicewengyee

You marked your question itself as the right answer. Could you correct that please.

Nikhil

Re: SAC: Calculate number of headcount using a calculated measure in model

former_member837245 — Thu, 23 Feb 2023 01:31:12 GMT

Hi Nikhil,

I cant find the vote options under the comments you provided. Is there anywhere else for me to vote your answer?

Thank you.

Best Regards,

Yanice

Re: SAC: Calculate number of headcount using a calculated measure in model

N1kh1l — Thu, 23 Feb 2023 04:30:12 GMT

yanicewengyee

I have converted my comments to answers. You can upvote/accept if it answered your query.

Nikhil