I am trying to expand this vision by also grouping to see multiple selected months. On the left is the current version and on the right is a draft of the structure I am trying to achieve.
I added a Start Date and End Date control which were used to group the data along with the locations.
The problem I have is that I cannot calculate the columns such as Year to Date Sales/Last Year Sales/Same Month Last Year Sales, because now the values are filtered by each month.
I tried make the calculations using PRE_FILTER, but then all the filters would be disabled, including Service Category, Location etc.
Is there a way to only ignore the filter associated with the grouping by date for a calculated field?
Thank you!
Hi @rusu2k - If you want to include the date field in your visual, we will have to do the YTD and other period calculations differently. First thing is rather than using your date controls as regular filters (which are removing the other rows from the underlying query and breaking the YoY types of calcs), we need to make them ‘hide’ the time periods from the visual, not actually filter it out from teh query. There is a trick to do this you can read up on here: How do I superimpose a period over period in a visual without having the period that I'm comparing it to in the visual? - #4 by Jesse
Not exactly sure how your are doing the YTD calcs right now (feel free to share the formulas you are using), but try using the runningSum for YTD and periodOverPeriodPercentDifference for the % change formulas. periodOverPeriodPercentDifference - Amazon QuickSight
Hi @Jesse, thank you for answering.
At the moment I am not using the date controls as regular filters, I was using them in a calculated field to get all the months between those two date values and then use that field to group by date. The problem was that the grouping(with the implied filtering) is applied when adding that field in the Rows field wells and I am trying to somehow avoid that in my year calculations columns.
At the moment I am using these formulas to get the values for specific time periods dynamically compared to the month selected in the Control Parameter.
For example for Last Year Revenue I am using sumIf({Amount}, extract('YYYY', {Date}) = extract('YYYY', addDateTime(-1, 'YYYY', TimeParameter))),
Last Year Previous Month: ifelse(extract('MM', {Date}) = extract('MM', TimeParameter) AND extract('YYYY', {Date}) = extract('YYYY', addDateTime(-1, 'YYYY', TimeParameter)), {Amount}, 0).
And I am using those fields to manually compare the periods in the YoY fields.
I will try to change these calcs and use the formulas suggested, thank you!
I see, so they arent filter controls but parameter controls you are using in your calc. That is a great way to go if you arent going to use the date field in your visual, but if you do then we need to use Table Calculations to reference values on other rows (across dates). Functions like Lag, RunningSum, periodOverPeriodPercentDifference, etc are all Table Calculations and will allow you to do math using numbers from other rows in the table. Once you get the numbers right, then use the ‘date hider’ trick to get rid of of the period you dont want to show in the visual (the numbers will still be ‘available’ for your calcs to reference, but they will be hidden).
