how to I filter data from pivot view post aggregation. The use case is I’m pulling a couple of years data to do this years’ week vs last year same week comparison and in doing so it is also pulling last year vs last to last year which I dont want to display
Welcome to the QuickSight community!
Can you please try the following approach, you can create a calculated field to keep only data for this year and last year. This approach ensures that your pivot table only shows the current year’s data compared to the previous year without pulling in data from earlier years.
Example : (Syntax may little vary - replace the date field from your dataset)
ifelse(extract('YYYY', {date_field}) = extract('YYYY', now())
OR extract('YYYY', {date_field}) = extract('YYYY', now()) - 1,
'Valid', 'Ignore')
Please refer the below QuickSight documentation and community post this might be helpful for you.
Hope this helps!
Hi, thanks for the response.
I’m using periodOverPeriodLastValue formula in my pivot to get the difference between this year’s week vs last year same week. I tried using the calculated field but I’m still seeing 2023 data with null 2022 data since there is an overall filter to get only 2 years of data.
I only want to output 2024 data represented in ‘row’ format.
My other use case is trailing six weeks data for which I need to filter for last 6 week’s data and using periodoverperiod formula to get the difference. In this I only want to output current week and not the last six weeks.
Please take a look at the below QuickSight documentation. It includes all the period functions along with examples, which should help you find the correct solution.
Hi @nishant_shah,
It’s been awhile since we last heard from you, did you have any additional questions regarding your initial post?
If we do not hear back within the next 3 business days, I’ll go ahead and close out this topic.
Thank you!
Hi @nishant_shah,
Since we haven’t heard back, I’ll close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.
Thank you!
Hey Brett, I’m good closing and thanks for following up.