PaPm cloud Count number employees or other field

[juancortes]

Dear experts

I try in SAP Profitability and Performance Management Cloud a model Join or a query do a count of a field for example count number employees

year-month area   employee    prodcut
202309      01       Michael      A1
202309      01       Paula        A2
202309      02       Michael      A2
202309      01       Michael      A2
202309      01       Pedro        A2
202309      02       Michael    A1

The result I need is for example

Area count_employees
01    3
02    1

Year-month count_emp
202309       3

Currently when I use count(distict EMPLOYEE) formula in a model join, the result is count the number field return the all join

Area count
01    4
02    2

year-month   count
202309         6

Currently I use count(distinct <name field>) but the behavior is that count number flied return of the model join.

Best,

Juan

[ZYT]

Hi @juancortes,

I would suggest you to use a window aggregate function to count the employee numbers in a (model) calculation (in universal model). According to your requirement following window function might be used:

COUNT(DISTINCT EMPLOYEE) OVER (PARTITION BY AREA) 

COUNT(DISTINCT EMPLOYEE) OVER (PARTITION BY YEAR-MONTH)

I hope this would be helpful.

Best regards,

Zhiyi

[Paulo_Moncada]

Hi Juan,

For this requirement I would suggest you try column pivot if you are in show screen and play around it.

I hope this helps!
Paulo M.