Help, help with calculated fields for date comparison

Question & Answer
, , , , , , , , ,
1

Hi guys,

I must created some calculated fields to compare datas. Could you help me, please?
I thank you so much.

Create calculated fields in QuickSight to compute:

The “current date” is not the system current date, but rather the maximum date present in the dataset.

  1. “Day” – The financial value for the current day.

  2. “Day minus 7” – The financial value from 7 days prior to the current date.

  3. “Week over Week” – The percentage difference in financial value comparing the current week to the previous week.
    Example: if today is a Wednesday, you should compare the financial value of this week up to Wednesday with the financial value of the previous week up to Wednesday.

  4. “Current Month” – The financial value for the current month up to the present date.

  5. “Month over Month” – The percentage difference in financial value between the current month and the previous month.
    Example: if today is the 10th of the month, compare the financial value of the current month up to the 10th with the financial value of the previous month up to the 10th.

  6. “Current Year” – The financial value for the current year up to the present date.

  7. “Year over Year” – The percentage difference in financial value comparing the current year to the previous year.
    Example: if today is May 10th, 2025, compare the financial value of this year up to May 10th, 2025 with the financial value of the previous year up to May 10th, 2024.

Important Note:
The “current date” is not the system current date, but rather the maximum date present in the dataset.
For all calculated fields, you must include a check to ensure that the “current date” corresponds to the maximum date in the dataset.

Fields:

  • Date: Maximum date in the dataset (considered as the “current date”)
  • Value: Financial value
2

Hi @July ,

You can refer to the user guide documentation available for date functions to use what fits the requirement. For e.g. for period over period difference you can use periodOverPeriodDifference - Amazon QuickSight.

I would suggest to get started on exploring the functions and we can provide further support if you get stuck with errors.

You can also leverage the build Calculation function powered by Q to create your calculated fields!

image
image1312×191 15.8 KB

Thanks,
Prantika

1 Like
3

The examples in the documentation are sometimes very simple. The difficulty is creating the partial comparison.

4

Hi @July, have you tried creating a field to obtain the max date in the dataset in this way: maxOver({Date}, [], PRE_AGG)

Where Date is the field indicating the date in your rows.
Then you add calculated fields for other fields based on this value.

Let me know if this helps.

Andrea