QuickSight function to find increase in hours

[Asfak]

I have a single dataset with client details and service hours details. I want to find the following metrics in QuickSight using calculated fields:

1. New active client hours: A new active client is defined as one whose earliest service date falls within that month.
2. Variance for new hours MoM (e.g., June to July).
3. New client numbers.
4. Variance for new client numbers MoM (e.g., June to July).

I’m able to get the client acquisition date using the formula below, but it doesn’t work for calculating the service hours for those clients for the months:
minOver(ServiceStartDate, [Clientid], PRE_AGG)

Also, has anyone done reporting for scenarios like below. If yes, could you please guide on this? I can write an sql view and create a report out of it but I dont want to replicating the datasets with such aggregations. Instead i would like to use my existing dataset and create Quicksight reports using calculated fields/functions.

1. Increase in hours from existing clients
2. Hours from new clients
3. Like for like hours
4. Decrease in hours from existing clients (negative)
5. Loss of hours from discharged clients (negative)

[Brett]

Hi @Asfak,
There’s quite a bit to this question so I’ll try and break down a bit but without knowing your field options within your dataset, it may be hard to fully assist.

1. You’ll need to start by creating a calculated field to get earliest service date using minOver. Something like:
   minOver({service_date}, [{client_id}])
   *Again, this would be dependent on your field options.

Then you can use that in an ifelse statement with extract.
Which may look something like:

ifelse(
  extract("MM", {earliest_service_date}) = extract("MM", {service_date}) and
  extract("YYYY", {earliest_service_date}) = extract("YYYY", {service_date}),
  {hours},
  0
)

2. Regarding variance for new hours MoM, create a calculated field for new hours by month:
   sumOver({new_active_client_hours}, [{service_date}])

Then you’ll create a calc. field for new hours of previous month, which you should be able to achieve with sumOver and ifelse.

3. For new client numbers, start by creating a calculated field to find out if client is new. You could try something like:

ifelse(
  extract("MM", {earliest_service_date}) = extract("MM", {service_date}) and
  extract("YYYY", {earliest_service_date}) = extract("YYYY", {service_date}),
  1,
  0
)

Then, you can do a new client count with a sumOver calculation. Something like:
sumOver({is_new_client}, [{service_date}])

4. You can follow similar steps that you did for #2.

As mentioned above, without knowing your available field options within the dataset, it makes this a little trickier. After trying these out, feel free to upload a sample analysis to QuickSight Arena view if you have any remaining questions as that will allow easier assistance.

Thank you

[Asfak]

Hi @Brett , thanks for your response and it worked. I would like to ask how to achieve below metrics in QS.

1. Increase in hours from existing clients
2. Hours from new clients
3. Like for like hours
4. Decrease in hours from existing clients (negative)

[Asfak]

A= raw data available
B= Expected report by individual client level
C= Expected report by monthly.

[Brett]

Hi @Asfak,
First off, you’ll want to create a Month calculated field for any future monthly partitions, which you can achieve using the truncdate function.

In regards to Question 1 (and 4)…looking at what you’d like to achieve, you’ll need to create separate calculated fields for ‘increase’ and ‘decrease’ if you’d like them to have their own columns. I’d suggest exploring the sumOver calculation for these and partition by your month calculated field.

2. I don’t see anywhere on your example where you’re utilizing the hours from new clients…do you just want an overall count of unique new clients or what are you trying to achieve for this?

3. I’m not sure what you mean by like for like hours so please explain further what this would do?

[Brett]

Hi @Asfak,
Following up here as we haven’t heard back. If we do not hear back within the next 3 business days, I’ll go ahead and close out this topic.

Thank you!

[Brett]

Hi @Asfak,
Since we haven’t heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new topic in the community and link this discussion for any relevant information that may be needed.

Thank you!