We need to calculate the Actual Amount in Amazon QuickSight while ensuring the below mentioned requirements

Question & Answer
, , , , , ,
1

We need to calculate the Actual Amount in Amazon QuickSight while ensuring the following:

  1. Merging Dates:
  • Use date_created if deact_date is NULL.
  • Use deact_date if date_created is NULL.
  • Only include rows where date_created and deact_date fall in the same month or if one of them is NULL.
  1. Activated Amount:
  • Calculate based on date_created.
  1. Deactivated Amount:
  • Calculate based on deact_date.
  1. Final Actual Amount:
  • Combine activated and deactivated amounts for valid merged dates.
2

Hi @Deepshikha,

Ideally you can do this as part of your dataset. I am not sure what is the source of your data. If it is a database a Custom SQL can handle all this easily.

However, if you are not familiar with SQL you can create calculated fields in your dataset to do these calculations and get the results you want.

Regards,
Giri

3

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

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

Thank you!