Pivot table - showing data being filtered off

[lbl]

Dataset is from a csv file with the following data.
invoicedate,Product,Sales SGD,month
1/11/2024,A,100,1
1/11/2024,B,200,1
2/12/2024,A,300,2
2/12/2024,C,400,2
3/13/2024,A,500,3
3/13/2024,B,100,3
4/14/2024,A,200,4
4/14/2024,B,300,4
5/15/2024,A,400,5

Calculated field SalesPrevMth is created as “lag(sum({Sales SGD}), [invoicedate ASC], 1, )”
Pivot table is created as below and working fine.

However, after Jan 2024 is filtered off. SalesPrevMth and Sales % become blank. How to still show them even when Jan 2024 is not showing?

[Brett]

Hi @lbl,
The February values (or rather January’s since it’s previous month), is not populating because the lag calculation is a table function; meaning it can only access the data that’s not filtered out.

Below is a really useful guide to order of operations within QuickSight as well as a further breakdown of various Period over period computations.

Check these out and let me know if you have any additional questions.

Order of evaluation in Amazon QuickSight - Amazon QuickSight

Period Over Period and Other Date Comparison Calculations (YoY, MoM, YTD, Rolling 90 days, etc)

Often you may want to create tables and other visuals which display multiple fields that are all ‘filtered’ to different date periods, as well as period-over-period fields. However we can’t use normal filters for this since filters get applied to all fields in the visual. Conceptually in this solution we are building the filters directly into the calculated field itself (using the ifelse logic), so that each field can have a different ‘filtered’ time period. You can then do whatever math you wan…

[lbl]

hi @Brett
The resource shared does not work for my case.
As the resource solution is based on only a single month, while I have rows of Month.

Any other alternatives?

Thanks!

[lbl]

hi @Brett
Any update on this, please.
Thank you.

[Brett]

Hi @lbl,
Apologies for the delayed response. After running some additional tests, I think this is limitation that does not have a current work around. QuickSight does not allow you to utilize PRE_AGG or PRE_FILTER with the lag function, therefore making it hard to incorporate data that you’ve already filtered out of the visual.

The best work around I can suggest for this instance; add an additional field to your dataset that is labeled ‘prior month sales’ and then pulls the sales from dates registered to prior month.

Additionally, I’ve seen this scenario arise in the community before so this is a fairly common ask; so I’ll mark this as a feature request as it would be nice for an out of the box option to be added allowing you to incorporate prior month values in this instance.