Create a formula with a dynamic denominator based on Month

Q&A
, ,
1

I’m trying to create two separate calculated fields that have include a a dynamic input for that changes based on the transaction month. Is this possible? Here are the details:

1. Revenue per Business Day:

Field Definitions

  • Revenue is a calculated field, aggregated based on the claim ID
  • Business Day represents number of business days in the month, which is different for every month, as noted below.

Ultimate goal
To present a bar-chart that shows Revenue per Day, which is calculated as "Total Revenue for a month / # of business days in that month)

ifelse(
{MONTH} = ‘Jan-24’, 21,
{MONTH} = ‘Feb-24’, 20,
{MONTH} = ‘Mar-24’, 21,
{MONTH} = ‘Apr-24’, 22,
{MONTH} = ‘May-24’, 22,
{MONTH} = ‘Jun-24’, 19,
{MONTH} = ‘Jul-24’, 22,
{MONTH} = ‘Aug-24’, 22,
{MONTH} = 'Sep

2. Average Revenue per Claim
Field Definition

  • Calculated field: Estimated Revenue (a calculated field that aggregates revenue at the claim level. if the claim is paid, we pull the NET REVENUE field. If the claim is UNPAID, we want to pull a dynamic value based on month, per the below)

Ultimate goal
Present various visuals that include Estimated Revenue with the dynamic input, so I can forecast revenue at $100/claim in JAnuary and $200/claim in February, as an example.

2

hi @quicksight44

This should be possible by leveraging parameters, controls and calculated fields.

Have you attempted to create this analysis in QUickSight? Did you encounter any specific issues?

You can use arena to load some test data and try it out if you have doubts and would like to share your challenges with the community.

Thanks!
Ramon

3

Thanks @Ramon_Lopez - do you have any specific advice on how to build this?

For the first request: I created a field for “Business Days” as noted above, and then created a second calculated field that took Net Revenue x Business Days, but I am getting an aggregation error.

Let’s focus on the 2nd request, Average Revenue per Claim.

Ultimate goal
Present various visuals that include Estimated Revenue with the dynamic input, so I can forecast revenue at $100/claim in JAnuary and $200/claim in February, as an example.

I tried to create a calculated field that included the revenue estimate per month, and then use that to multiple # of visits in that month by the value. it is not working and I’m getting aggregation errors.

do you have any sepcific advice on how to build these analyses?

4

Hi @quicksight44

Would you be able to share the details of your calculated fields?

Revenue
Business Das
Revenue Per day

and some sample data of your dataset (I want to see how your MONTH looks like as well.

Regards,
Giri

5

Revenue
1. Estimated Revenue (New + Est) (1111/929)

Est Revenue (New) ($1111) + Est Revenue (Established) ($929)

  1. Est Revenue (New) ($1111

sumif(NETREVENUE,{VISIT_PYMT_STATUS}=“Paid” AND {NEW_OR_EST} = “NEW”)+(sumIf(NUMBEROFCHARGES,{NEW_OR_EST}=“NEW” AND (NOT({VISIT_PYMT_STATUS}=“Paid” OR {VISIT_PYMT_STATUS}=“Write-off” OR {VISIT_PYMT_STATUS}=“No Visit” OR {VISIT_PYMT_STATUS}=“Corporate Billing”)))*1111)

  1. Est Revenue (Established) ($929)

sumif(NETREVENUE,{VISIT_PYMT_STATUS}=“Paid” AND {NEW_OR_EST} = “Est”)+(sumIf(NUMBEROFCHARGES,{NEW_OR_EST}=“Est” AND (NOT({VISIT_PYMT_STATUS}=“Paid” OR {VISIT_PYMT_STATUS}=“Write-off” OR {VISIT_PYMT_STATUS}=“No Visit” OR {VISIT_PYMT_STATUS}=“Corporate Billing”)))*929)

Business Day per Month

Business Days =
ifelse(
{MONTH} = ‘Jan-24’, 21,
{MONTH} = ‘Feb-24’, 20,
{MONTH} = ‘Mar-24’, 21,
{MONTH} = ‘Apr-24’, 22,
{MONTH} = ‘May-24’, 22,
{MONTH} = ‘Jun-24’, 19,
{MONTH} = ‘Jul-24’, 22,
{MONTH} = ‘Aug-24’, 22,
{MONTH} = 'Sep…

Revenue per day:
{_1b. Estimated Revenue (New + Est)}/{Business Days}

But I receive this error: Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination.

6

Hi @quicksight44
Can you apply a sum function on both the nominator and denominator and see if that resolves your issue?

{_1b. Estimated Revenue (New + Est)}/{Business Days}

Regards,
Giri

7

Hi @quicksight44,
It’s been awhile since we last heard from you, did you have any additional questions or were you able to find a work around for your case?

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

Thank you!

8

Hi @quicksight44,
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.

Thank you!