Show Headcount based on Date Range Selected

Ankur

Hello Everyone,

I have been breaking my head over last 2 weeks looking for a probable solution to a scenario that I have. I tried ChatGPT as well but nothing worked.

So, I have a Headcount App where I show Headcount based on various dimensions like - Geo, Region, Country, Gender, Leader, Job Role, etc, etc.

Currently the Headcount is showing for the Current Month as I have selected 'This Month' option. And whenever user select a past Date, then my cards goes blank. Changing the Date Range from 'This Month' to 'All Time' does not help, as it sum up all the month Headcount and shows an inflated number, which is not expected.

What I am looking for is a Dynamic Beast Mode which will Sum the Headcount based on the date selected. If No Date is Selected then it will show the most Recent Headcount, else it will show Headcount as per the date selected.

In the below attached Excel file, I have created a simulation to show what exactly I am looking for. Please choose a date in cell E2 and it will change the numbers. Please Note the Geo is just an example I have given.

Dynamic Headcount.xlsx

I have created a Beast Mode and it does the work that I wanted but it splits the Geos.

Beast Mode I created :

CASE 
  WHEN    
    `Date` = MAX(`Date`) OVER (PARTITION BY `Geo`)        
    THEN SUM(`Headcount - Actual`)
  ELSE 0
END

Output I got is :

Ideal Output required :

I am in desperate need of a solution to handle this scenario.

Data_Devon

Hello Ankur!

A Beastmode might be an unnecessary solution here.

You can set the "Geo" (or other categorical data) on the X Axis and the "Count" of a date on your Y Axis.

You can then select whatever date you want in the date selection of the Chart itself.

This will default the date range for an untouched card.

Then, you can create a filter card to allow end users to select the date that they want to look at. This filter card does not need to be powered off a Beastmode, but rather can use the same data that is powering the output chart.

NB: If you date data is at a daily level but you want filtering ability at the month level, then a simple Beastmode can group those dates up to the month level, like so:

MONTH(`Date`)

Selecting default date range on chart:

Slicer/Filter using date field:

Data_Devon

Hello Ankur!

A Beastmode might be an unnecessary solution here.

You can set the "Geo" (or other categorical data) on the X Axis and the "Count" of a date on your Y Axis.

You can then select whatever date you want in the date selection of the Chart itself.

This will default the date range for an untouched card.

Then, you can create a filter card to allow end users to select the date that they want to look at. This filter card does not need to be powered off a Beastmode, but rather can use the same data that is powering the output chart.

NB: If you date data is at a daily level but you want filtering ability at the month level, then a simple Beastmode can group those dates up to the month level, like so:

MONTH(`Date`)

Selecting default date range on chart:

Slicer/Filter using date field:

Ankur

Hi @Data_Devon,

Thanks for your guidance. However, I can't do count of Date as the data is stored at Last Dat of the Month. So per grain, I will only have 1 row but in that grain the Headcount might be 23k or more.

Below is a sample data example with only few dimensions I have added. I have more than 20 Dimensions/ Category like - Business Unit, Vertical, Industry, Gender, Billable/Non-Billable, Agent/Non-Agent, Site, Campus, Region, Cost Center Leader, Cost Center Family, Cost Center, Job Family Group, Job Family, Job Profile, Job Category, etc, etc. The Dimension/ Category list is huge.

So, the only option I have is to find a Beast Mode solution to handle this uniquely. Can you advise something on that line.

Date	Geo	Country	City	Leader	Headcount
31-Jul-24	Geo 1	Country 1	City 1	Leader 1	79,409
31-Jul-24	Geo 1	Country 1	City 1	Leader 2	46,203
31-Jul-24	Geo 1	Country 1	City 2	Leader 1	63,652
31-Jul-24	Geo 1	Country 1	City 2	Leader 2	83,322
31-Jul-24	Geo 1	Country 2	City 1	Leader 1	58,683
31-Jul-24	Geo 1	Country 2	City 1	Leader 2	88,591
31-Jul-24	Geo 1	Country 2	City 2	Leader 1	45,094
31-Jul-24	Geo 1	Country 2	City 2	Leader 2	54,153
31-Jul-24	Geo 2	Country 1	City 1	Leader 1	51,117
31-Jul-24	Geo 2	Country 1	City 1	Leader 2	24,844
31-Jul-24	Geo 2	Country 1	City 2	Leader 1	45,344
31-Jul-24	Geo 2	Country 1	City 2	Leader 2	44,655
31-Jul-24	Geo 2	Country 2	City 1	Leader 1	83,231
31-Jul-24	Geo 2	Country 2	City 1	Leader 2	75,471
31-Jul-24	Geo 2	Country 2	City 2	Leader 1	35,495
31-Jul-24	Geo 2	Country 2	City 2	Leader 2	42,791
31-Aug-24	Geo 1	Country 1	City 1	Leader 1	91,242
31-Aug-24	Geo 1	Country 1	City 1	Leader 2	34,744
31-Aug-24	Geo 1	Country 1	City 2	Leader 1	46,377
31-Aug-24	Geo 1	Country 1	City 2	Leader 2	45,318
31-Aug-24	Geo 1	Country 2	City 1	Leader 1	97,383
31-Aug-24	Geo 1	Country 2	City 1	Leader 2	76,295
31-Aug-24	Geo 1	Country 2	City 2	Leader 1	58,792
31-Aug-24	Geo 1	Country 2	City 2	Leader 2	29,187
31-Aug-24	Geo 2	Country 1	City 1	Leader 1	97,166
31-Aug-24	Geo 2	Country 1	City 1	Leader 2	48,683
31-Aug-24	Geo 2	Country 1	City 2	Leader 1	90,975
31-Aug-24	Geo 2	Country 1	City 2	Leader 2	85,874
31-Aug-24	Geo 2	Country 2	City 1	Leader 1	67,509
31-Aug-24	Geo 2	Country 2	City 1	Leader 2	80,923
31-Aug-24	Geo 2	Country 2	City 2	Leader 1	58,346
31-Aug-24	Geo 2	Country 2	City 2	Leader 2	79,714
30-Sep-24	Geo 1	Country 1	City 1	Leader 1	97,704
30-Sep-24	Geo 1	Country 1	City 1	Leader 2	51,874
30-Sep-24	Geo 1	Country 1	City 2	Leader 1	27,560
30-Sep-24	Geo 1	Country 1	City 2	Leader 2	80,960
30-Sep-24	Geo 1	Country 2	City 1	Leader 1	37,837
30-Sep-24	Geo 1	Country 2	City 1	Leader 2	56,434
30-Sep-24	Geo 1	Country 2	City 2	Leader 1	93,185
30-Sep-24	Geo 1	Country 2	City 2	Leader 2	55,476
30-Sep-24	Geo 2	Country 1	City 1	Leader 1	41,983
30-Sep-24	Geo 2	Country 1	City 1	Leader 2	37,506
30-Sep-24	Geo 2	Country 1	City 2	Leader 1	41,100
30-Sep-24	Geo 2	Country 1	City 2	Leader 2	90,355
30-Sep-24	Geo 2	Country 2	City 1	Leader 1	51,719
30-Sep-24	Geo 2	Country 2	City 1	Leader 2	54,918
30-Sep-24	Geo 2	Country 2	City 2	Leader 1	71,299
30-Sep-24	Geo 2	Country 2	City 2	Leader 2	67,377
31-Oct-24	Geo 1	Country 1	City 1	Leader 1	90,191
31-Oct-24	Geo 1	Country 1	City 1	Leader 2	67,140
31-Oct-24	Geo 1	Country 1	City 2	Leader 1	44,796
31-Oct-24	Geo 1	Country 1	City 2	Leader 2	48,296
31-Oct-24	Geo 1	Country 2	City 1	Leader 1	27,494
31-Oct-24	Geo 1	Country 2	City 1	Leader 2	42,765
31-Oct-24	Geo 1	Country 2	City 2	Leader 1	87,312
31-Oct-24	Geo 1	Country 2	City 2	Leader 2	36,939
31-Oct-24	Geo 2	Country 1	City 1	Leader 1	30,532
31-Oct-24	Geo 2	Country 1	City 1	Leader 2	37,336
31-Oct-24	Geo 2	Country 1	City 2	Leader 1	45,221
31-Oct-24	Geo 2	Country 1	City 2	Leader 2	45,803
31-Oct-24	Geo 2	Country 2	City 1	Leader 1	90,394
31-Oct-24	Geo 2	Country 2	City 1	Leader 2	90,776
31-Oct-24	Geo 2	Country 2	City 2	Leader 1	36,151
31-Oct-24	Geo 2	Country 2	City 2	Leader 2	90,583
30-Nov-24	Geo 1	Country 1	City 1	Leader 1	30,260
30-Nov-24	Geo 1	Country 1	City 1	Leader 2	88,629
30-Nov-24	Geo 1	Country 1	City 2	Leader 1	54,578
30-Nov-24	Geo 1	Country 1	City 2	Leader 2	33,026
30-Nov-24	Geo 1	Country 2	City 1	Leader 1	58,995
30-Nov-24	Geo 1	Country 2	City 1	Leader 2	76,330
30-Nov-24	Geo 1	Country 2	City 2	Leader 1	58,394
30-Nov-24	Geo 1	Country 2	City 2	Leader 2	30,090
30-Nov-24	Geo 2	Country 1	City 1	Leader 1	55,721
30-Nov-24	Geo 2	Country 1	City 1	Leader 2	96,557
30-Nov-24	Geo 2	Country 1	City 2	Leader 1	29,502
30-Nov-24	Geo 2	Country 1	City 2	Leader 2	97,662
30-Nov-24	Geo 2	Country 2	City 1	Leader 1	69,607
30-Nov-24	Geo 2	Country 2	City 1	Leader 2	91,842
30-Nov-24	Geo 2	Country 2	City 2	Leader 1	92,923
30-Nov-24	Geo 2	Country 2	City 2	Leader 2	29,586
18-Dec-24	Geo 1	Country 1	City 1	Leader 1	28,933
18-Dec-24	Geo 1	Country 1	City 1	Leader 2	36,432
18-Dec-24	Geo 1	Country 1	City 2	Leader 1	75,441
18-Dec-24	Geo 1	Country 1	City 2	Leader 2	88,352
18-Dec-24	Geo 1	Country 2	City 1	Leader 1	29,141
18-Dec-24	Geo 1	Country 2	City 1	Leader 2	34,218
18-Dec-24	Geo 1	Country 2	City 2	Leader 1	34,153
18-Dec-24	Geo 1	Country 2	City 2	Leader 2	49,477
18-Dec-24	Geo 2	Country 1	City 1	Leader 1	91,927
18-Dec-24	Geo 2	Country 1	City 1	Leader 2	58,746
18-Dec-24	Geo 2	Country 1	City 2	Leader 1	29,858
18-Dec-24	Geo 2	Country 1	City 2	Leader 2	72,972
18-Dec-24	Geo 2	Country 2	City 1	Leader 1	71,326
18-Dec-24	Geo 2	Country 2	City 1	Leader 2	88,068
18-Dec-24	Geo 2	Country 2	City 2	Leader 1	29,303
18-Dec-24	Geo 2	Country 2	City 2	Leader 2	95,516