When I click on the month bar, I have a navigation action passing the Month and Year to another worksheet where I have a data table. I need to filter this data table using the parameters, but in this table I have the actual quantity of the month, the quantity of the previous month and the quantity of the same month of the previous year. When I filter the month, the previous month calculation doesn’t work because I’m only filtering the actual month.
How can I resolve this problem? How can I filter the month that the user clicked and my table woks fine calculating everything correctly?
Hello @Mardcp , welcome to the Quicksight community!
To make sure I understand, when you say the quantity of the month do you mean, for example, January =1, February = 2, March = 3, etc…?
And if so, what does your calculation look like? My recommendation as of now is to make that calculation an ifeslse statement but it will be easier for me to help if you could provide an anonymized view of that calculation.
Hi @Mardcp
its an common challenge. Due to filter on month 1 and year 2023 you can’t show data for 1.2022 or 12.2022. You need to bring the data on the same date (1.2023) or extend the filter.
There are a few “workarounds” and hints in the community.
BR
Hi @ErikG. It’s a very common challenge, like you said. I’ve worked with other tools like Tableau or PowerBI and it was very easy to do this. But in Quicksight I couldn’t solve this problem.
Can you give me an example of alternative solutions please?
@ErikG, I’m sorry to ask again, but when I created the new field and checked the month the calculation fields didn’t work again because I only filtered for a month.
I don’t understand how to filter only on the table and not on the data I’m using in the calculated fields
In this spreadsheet I have a data table that is filtered by date according to the parameter in the previous chart. In this case I clicked on 2023, March. Following @Erik’s example, the calculations work, but my table shows data from 2023, Feb (previous month) and 2022, March (previous year), but I would like only the month of 2023, March with all calculated fields working correctly.
Hello @Mardcp ! I think I understand what you are saying.
You may need to experiment to make this dynamic but you could try using a relative date filter that is not on the sheet but applied to the table that says what ever your parameter date filter is, relative to the current month of your selection.
Actual Month → Sum(qty)
Previous Month → periodOverPeriodLastValue(sum({qty}),{data_referencia},MONTH,1)
var. Month → periodOverPeriodPercentDifference(sum({qty}),{data_referencia},MONTH,1)
pervious Year → periodOverPeriodLastValue(sum({qty}),{data_referencia},YEAR,1)
var Year → periodOverPeriodPercentDifference(sum({qty}),{data_referencia},YEAR,1)
