I have a chat bar with year in X and Month in group like:

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.

1 Like

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

No. When I refer to quantity, I mean quantity of sales, for example.

I need to work with the number of sales from the current month, the previous month and the previous year. Could I explain better?

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?

1 Like

Hi @ErikG.

Perfect, I did it! Thanks!

But, I need to show in my table only the selected month. Per example: I selected 2023, March, so:

• I need 2023,Feb do calculate the previous month
• I Need 2022,March to calculate the previous year

However, in my data table I would like to show only 2023,March and the calculate fields. Is it possible?

You could create a 0/1 field for month you want to display and filter the new field.

1 Like

@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

Hey @Mardcp!

Hey @duncan , how are you?

I’m using the periodOverPeriodPercentDifference function. I think that I can’t use the PRE_FILTER in this function, right?

Hey @Mardcp ! I did not realize you were using that calculated field.

When you filtered by month, did you follow the example that was laid out in the post that @ErikG shared?

Hi @duncan,

Lest’s try explain better.

I have the chat bar where I have a action to navigate to other sheet.

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.

In the table you can see the 2023,Feb (previous month).

In case I wasn’t clear, let me know.

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.

Something like this:

Hi @duncan

When I did it, my calulate fields didn’t work:

Hey @Mardcp ,

Can you share anonymized looks at the calculated fields you are using.

Yes, sure.

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)

1 Like

Hi @ErikG and @duncan.

Any other possibilities to only show the month I selected in the chart but keep my calculated fields working?

Regards

Hi @Mardcp
recently i did something like

``````periodOverPeriodDifference(sum(ifelse({date_ordered}>=parseDate(concat(toString(\${ComparisonYear}),"/","01","/","01"),"yyyy/MM/dd") AND {date_ordered} <= parseDate(concat(toString(\${ComparisonYear}),"/",toString(extract("MM",\${ReportingDate})),"/",toString(extract("DD",\${ReportingDate})),"yyyy/MM/dd")),measure,0)), {date_ordered}, WEEK,53)*-1,
``````

to show the PY data on the same dates as the selected year.

so green is the current year and gray the prev year data (on the same date)

that way i can filter

BR

1 Like

Hello @Mardcp !

Were you able to try @ErikG 's solution? To me this looks like it should achieve what you are hoping to accomplish.