Compare the value of a field for two different dates (YTD, and this month compared to same month of last year)

Question & Answer
, ,
1

Hi,
I have a dataset which contains a date field as well as an integer field which contains the number of companies at that date (simplified).

The date field contains the date of each month in the time period covered by the dataset, e.g., the last three years, where in each month the number of companies was counted. For example, 27 December, 2023, 27 January 2024 or 27 February 2024, etc. (It’s always the 27th.)

I now want to show a table which shows for the current month (or a month which can be selected via a control):

  • number of companies in the last month where they were counted (most recent date) - that seems to be easy, just the value from number of companies filtered for the current month
  • the change in percent compared to the same month of last year
  • the change in percent compared the end/December of last year (YTD)

I wonder how the calculated fields for the last two values look like? It seems not trivial to compute the values in QuickSight. (It would be easy if in each row in the dataset, there was also a column for the corresponding number at December of last year and another column for the corresponding number in the same month of last year, but the dataset does not contain such columns).

I have another dataset with an additional field which contains the category of the companies counted. I would also like to show a visual with the values from above, but broken down for each category of companies.

Any help is very much appreciated!

2

Hi

Have a look into this and you will get an overview.

YTD Comparisons - Question & Answer - Amazon QuickSight Community

1 Like
3

Thank you, that helped me. But is it not possible to show such numbers like YTD etc. only for one specific month? If I filter the table for one month (e.g., the current one), it can’t calculate the difference to last year anymore, because that month is not included in the data for the table anymore.

Can I somehow create a visual, preferably a table, which shows such numbers like current value, difference YTD, difference same month last year only for one month / the current month?

4

Hi @DanielH,
It’s been awhile since last communication took place on this thread; were you able to find a work around for your case or are you still working on this?

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

Thank you!

5

Hi @Brett,

Actually, I got help in another thread which helped me to find a solution:

So this topic can be closed.

Thanks!

2 Likes