Synchronizing Event Costs and User Payments for Comparative Analysis

[shirakoresh]

I have a specific use case and would be happy to receive your assistance. I have an event table where each row represents an event (with an event_id field), including date and cost fields. I have already created calculated fields to extract the month, quarter, and year from the date, though currently, my data only spans from last February.

Now, for my question: I would like my dashboard users to enter a free-text value into a control indicating how much money they intend to spend in a year. To facilitate this, I have created a “payment” parameter and a control that allows users to input their yearly payment amount.

Subsequently, I aim to display the percentage of event-related expenses compared to the user’s specified yearly payment, broken down by month, quarter, and year. However, I am encountering difficulties aligning the timeframe of the event costs with the timeframe of the user’s payment.

Could someone kindly demonstrate how to achieve this synchronization?

Thank you for your assistance!
Shira

[SD_QS]

Hi @shirakoresh Could you please demonstrate with an example (perhaps a screenshot of a mockup in excel) what you’re trying to achieve? Specifically, what do you mean by:

However, I am encountering difficulties aligning the timeframe of the event costs with the timeframe of the user’s payment.

This will allow the community members to provide relevant inputs, thanks!

[Karthik_Tharmarajan]

Hi @shirakoresh - Can you share sample data and/or screenshots of what you are trying to achieve?

Regards,
Karthik

[shirakoresh]

Hi
@Karthik_Tharmarajan , @SD_QS Thank you!

I’ve created mock data to better explain my issue. Here’s a snapshot of my raw data:

I enhanced this data by adding fields for “quarter”, “year”, and two fields calculated based on a parameter:

Subsequently, I designed a dashboard based on this data:

I am attempting to introduce a calculated field, named ‘ROI’, influenced by the free-text parameter I integrated earlier, titled “pay”:

However, when I incorporate this to my table, the ROI values appear to not be aligned with the selected period:

For instance, the ROI for Arik in the first row is 3,733. This value is derived from the equation: 1120/30*100 = 3,733. But in reality, the ROI should be: 1120/2.5×100 = 44,800. This is because, although the customer pays 30 for a year, the ROI I am attempting to calculate is on a monthly basis. So, the monthly pay should be 30/12​ = 2.5.

I tried making the following adjustments:

Yet, there seems to be an error somewhere. The ROI I’m getting is:

The calculation appears to be: 1120/2*100 = 56,000. I’m unsure why this is the case.

I hope this clarifies the issue I’m facing. Thank you!

[Karthik_Tharmarajan]

@shirakoresh - Thanks for additional context! Is sounds like the ROI calculation is not considering the decimal value. In your example when you divide 30 by 12 it should result in 2.5 however QuickSight calculation is returning the integer value 2.
Try modifying your ROI calculation to include ${pay}/12.0 for month and ${pay}/4.0 for quarter. This should result in a decimal value. Hope this helps.

Regards,
Karthik

[shirakoresh]

Hi @Karthik_Tharmarajan,

Thank you for your assistance! I have a follow-up question (please let me know if I should create a new post for it). I’m looking to calculate the ROI for a year. For this, I need the “event_potential_cost” for a full year. However, I only have data for a few months, not an entire year. I’d like to create a field named “avg_yearly_potential_cost”. Could you help me with the correct formula or calculation for this field?

Thanks again