1 Reply Latest reply on Apr 16, 2018 12:22 PM by Yuriy Fal

# Max Headcount for a Period

Hello Everybody,

Have been trying to achieve this in several ways but the performance seems a hindrance. Any inputs are highly appreciated. Trying to get the sum(Headcount) in an organization based on the Date. So we have data for every day at each employee level in the table.  Consider a dataset like this for 365 days, with about 80000 employees. We need to fetch the Max Headcount based on the last date of Time Period selected. If Year(Date) is selected, it needs to fetch the sum(Headcount) as of 31 Dec 2018; if drilled down to Quarter, the sum(Headcount) should show up the numbers as of 31 Mar 2018, 30 Jun 2018, 30 Sep 2018, 31 Dec 2018; if drilled to Month it should be the number of only the last date of a month.

If(Date = MAX(Date) then sum(Headcount) is the one working but with a lot of performance hit.

Tried LOD with {Include Date:Max(Date)} ; {Include Date : Sum(Headcount)} gives us the right results but again there is performance lag. We would need a formula like

{FIXED MAX(Date) : Sum(Headcount)} which does not work as aggregated values cannot be defined in the place of a dimension.

Empl IDDate
101/01/20181
201/01/20181
301/01/20181
401/01/20181
501/01/20181
102/01/20181
202/01/20181
302/01/20181
402/01/20181
502/01/20181
103/01/20181
303/01/20181
403/01/20181
503/01/20181
603/01/20181
703/01/20181
803/01/20181
903/01/20181
1003/01/20181

Regards,

Anupam Singh

• ###### 1. Re: Max Headcount for a Period

Hi Anupam,

The solution #9 from the Top 15 Tableau LOD Expressions would work.

Please find the attached as a simplified version of the above.

Yours,

Yuri