Navigation action using date parameters

Question & Answer
, , ,
21

Hi @ErikG and @duncan,

I tried this solution but it didn’t work for me.

In ErikG’s solution there is a time interval and uses a line graph.

In my case, I am using a table and I must only show the selected month and the calculation fields for the previous month and the previous year).

Follow my calculation fields:
Actual month: sum(ifelse( {data_referencia} >=truncDate(‘MM’, EndMonth) AND {data_referencia} <= EndMonth,{qtd_consulta_total},0))

Previous month: periodOverPeriodLastValue(sum(ifelse( {data_referencia} >=truncDate(‘MM’, EndMonth) AND {data_referencia} <= EndMonth,{qtd_consulta_total},0)),{data_referencia},MONTH,1)

When I filtered the 2023,March the result was:

image
image1052×643 34.1 KB

Any more options?

22

Hey @Mardcp !

At this point I’m curious if it would be better to totally change up how the calculated fields work from using periodOverPeriodLastValue to using the dates selected via the parameter as a filter in the calculated field using addDateTime.

For example, if you created date parameters you could change the calculated fields to something like below:

Previous Month:

sumif(
{quantityvalue}, 
{DateField} >= addDateTime(-1, 'MMM', ${DateParam1}) AND 
{DateField} <= addDateTime(-1, 'MMM', ${DateParam2})
)

You might need to experiment with this and your navigation actions, but the basic premise of my thinking here is telling Quicksight exactly what data to show based on two dynamic start and end points.

2 Likes
23

Hi @duncan,

I experimented it, but the problem persists.

Even with the use of the functions, I understand that it should work even with the filter for a single period.

Other DataViz tools allow this action type , filtering a period and presenting calculated fields from previous periods.

24

Hey @Mardcp !

Can you try the solution posted in this blog? I think it will get you the closest to your desired outcome.

1 Like