Help Needed with Calculating Percentages Based on Date Fields in QuickSight

Question & Answer
,
1

Hi everyone,

I’m working on a project in QuickSight and could use some assistance. I have two date fields: an Effective Date and an Expiration Date. Here’s what I need to achieve:

  1. Display a dollar amount based on the Effective Date Month.
  2. Display a dollar amount based on the Expiration Date Month.
  3. Calculate a percentage based on the formula: (Dollar Amount for Effective Month/ Dollar Amount for Expiration Month) * 100.
    I was able to create separate tables that displays the values but is struggling with combined view to display the percentage. I am new to QuickSight. Any guidance or examples would be greatly appreciated!

Thank you in advance for your help!
image

2

Hi @mintomichael and welcome to the QuickSight community!
Ideally, what would you like your end visual to look like? One table that has just 1 month field (instead of both Expiration and Effective) and then the available, renewed, and % renewed columns for a given month?

What issue are you running in to if you add all of these fields to one table as additional values?

3

I have tried adding Effective and Expiration months together but the value it returns are not accurate for Available or renewed. The issue is we could have dollar amounts associated with effective month that can potentially expire in a different month.
Ideally, I would like my visual to look like this. One table across.

|Column 1 | Column 2 | Column 3 | Column 4|
|--- | --- | --- | ---|
|Effective Month | Available | Renewed | % Renewed|
|Sep 2024 | 40,000,000 | 39,000,000 | 98%|
|Oct 2024 | 50,000,000 | 30,000,000 | 60%|
|Nov 2024 | 60,000,000 | 20,000,000 | 33%|
| |  |  | |
| |  |  | |
4

Hi @mintomichael,
Sorry for the delayed response!
So to understand your dataset a bit further, I’m guessing a row consists of a customer, and then there are columns/fields for expiration month, effective month, etc?
Could you share the calculated fields you’re using to get the available and renewed numbers?

Alternatively, if able to, this could be easier to assist if you upload a copy of your analysis with sample data to the QuickSight Arena view, that way I could test out a couple different scenarios to see what may work!

5

Hi @mintomichael,
It’s been awhile since we last heard from you, did you have any additional questions regarding your initial topic?

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

Thank you!

6

Hi @mintomichael,
Since we haven’t heard back, I’ll 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!