Hello,
I am trying to create a field in the data for showing the prior year sales where the year is defined as an integer, so I used the lag function but when I apply a filter on the year it doesn’t show the prior year data, so I tried using sumover with pre_filter option, but an error occurred that said ‘Please contact the Quicksight team to solve this issue’ can anyone help?
PS: below is the formula is used for this calculation where i grouped the sales into various levels.
thank you.
lag(sumover({sales},[],PRE_FILTER),[financialyear ASC],1,[{Level 2},{Level 3},{Level 4},{Level 5}])
I have reported this to the QuickSight team and will revert back when we have an update.
Thanks for letting us know and sorry you are having difficulty with this.
Hi @sherif.elshaer -
I’ve confirmed the lag function will allow you to nest sumOver within it without applying aggregation . Thank you for raising this concern here. I’ve reproduced the error and will be investigating.
To use a LAC-W within a table calculation you can use
lag(max(sumover({sales},[],PRE_FILTER)),[financialyear ASC],1,[{Level 2},{Level 3},{Level 4},{Level 5}])
For your use case, the lag function won’t help here since it’s a table calculation. What this means is that the data must be in your visual dataset, or to use an analogy the lag function can only access what’s “on the table”. Once you filter your visual to a specific year like 2016 then lag can’t access the 2015 rows. Check out this order of evaluation doc for more detail.
To accomplish what you want you’ll need to use parameters. Check out this awesome guide for some approaches Period Over Period and Other Date Comparison Calculations (YoY, MoM, YTD, Rolling 90 days, etc)
Example:
Step 1 - Create a Year Parameter and add a control for it.
Step 2 - Create a current year sales calculated field
c_sales_current_year
sumif(Sales,{c_extract_year}=${pYear})
Step 3 - Create a previous year sales calculated field
c_sales_previous_year
sumif(Sales,{c_extract_year}=${pYear}-1)
Result:
