Subtract Prior Month's Running Total Exits from Current Month's Running Total Entries

Q&A
, ,
1

Hello,

I am trying to create a chart that counts the number of clients active every month. This is determined using the client’s project start date and project exit date. They start being counted in the month of their project start date, and they stop being counted in the month following their project exit date. I am open to any ideas, but the current solution I’m working on is below.

I created calculations for the running totals of entries and exits by month, but I need to be able to subtract the previous month running total exits from the running entry total for the current month, and I can’t figure that out. In the example below, the running total of clients that have entered the project in April or prior is 116. I need to subtract the running total of exits through March (39) to get the active clients in April, which is 77.

image
image700×267 6.2 KB

2

Hi @ErinD

Welcome to the QuickSight community!

To calculate Active Clients each month, please try the following calculated fields. MonthNO ensures months sort chronologically.

MonthNO - 
ifelse(
  Month='January',1,
  Month='February',2,
  Month='March',3,
  Month='April',4,
  Month='May',5,
  Month='June',6,
  Month='July',7,
  Month='August',8,
  Month='September',9,
  Month='October',10,
  Month='November',11,
  Month='December',12,0
)

Running Sum Project Entries  - runningSum( sum({Project Entry}), [ MonthNO ASC ] )

Running Sum Project Exits - runningSum( sum({Project Exit}),  [ MonthNO ASC ] )

Prev Run Exits - coalesce( lag({Running Sum Project Exits}, [MonthNO ASC], 1), 0 )

Active Clients - {Running Sum Project Entries } - {Prev Run Exits}

image
image1572×681 77.2 KB

3

Thanks for your quick response. The issue I’m having is that in the raw data the dates are two different fields, and I can’t figure out a way to create a chart from the data. My solution was trying to use the running totals, but I don’t think I can make it work because the Month column doesn’t exist in my dataset. Is this possible to do using the raw data below?

The raw data looks like this:

image
image322×237 2.87 KB

4

Hi @ErinD

Please use the calculated fields below to extract the MonthNo and MonthName from the date field.

Example: Note - Replace the fields from your dataset

MonthNo  -  extract('MM', {Date Field}) // Project Entry Date or Project Exit Date

MonthName -
ifelse(
    {MonthNo}  = 1, 'Jan',
    {MonthNo}  = 2, 'Feb',
    {MonthNo}  = 3, 'March',
    {MonthNo}  = 4, 'April',
    {MonthNo}  = 5, 'May',
    {MonthNo}  = 6, 'June',
    {MonthNo}  = 7, 'July',
    {MonthNo}  = 8, 'Aug',
    {MonthNo}  = 9, 'Sept',
    {MonthNo}  = 10, 'Oct',
    {MonthNo}  = 11, 'Nov',
    {MonthNo}  = 12, 'Dec',
    NULL
)
5

I was able to make progress. My new issue is that the visualization is requiring all three fields to display. I want to create a bar chart of active clients by month, but I can’t do that with Month Number in the visual. Any ideas how to remove it? When I remove month number or month, I am receiving the error: Table calculation attribute reference(s) are missing in field wells.

image
image590×373 32 KB

6

Hi @ErinD

You can hide columns in a table visual. Please refer to the documentation below, it might be helpful for you.

7

Why not just use Month (MONTH) and Active Clients in Bar Chart? At this point you should already have calculated the active clients in the calculated field?

8

Hi @ErinD,

It’s been awhile since we last heard from you on this thread, did you have any additional questions regarding your initial post or were you able to implement a work around?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

9

Hi @ErinD,

Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you