QuickSight function to find increase in hours

Question & Answer
,
2

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
)
  1. 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.

  1. 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}])

  1. 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

1 Like