I have a specific use case where I need to show sum of Amount column for the past 5 periods. For confidentiality purposes, let’s say I have columns X, Y, and Amount. I want to have a control to let the users select what type of period they want to see (Month, Quarter and Year). I then want to show the sum of Amount for all X and Y combinations for 5 past periods (it can be one of Month, Quarter and Year based on what user selects). I’m able to achieve this in some hacky way. But here comes the actual problem.
The use case is, let’s say the current time is Nov, 2023, I want the column names of the past 5 periods to be
Nov 2023, Oct 2023, Sept 2023, Aug 2023, Jul 2023 in case of when the user selects period as Month,
Q4 2023, Q3 2023, Q2 2023, Q1 2023, Q4 2022 in case of when the user selects period as Quarter,
and 2023, 2022, 2021, 2020, 2019 in case of when the user selects period as Year.
Please help me with how to solve this use case.
Currently I want this to be working with X and Y, but the scope may increase to more than 2 column combinations though I don’t think this would be much of an issue.
Hi @iamtarun
I would create a visual for each date aggregation combined with an “last 5” filter based on a date parameter. You can set the date aggregation level for each visual.
Depending on an other parameter “select aggregation” you could then hide/show the corresponding visual.
Hi @ErikG, thanks for helping me out. The approach you suggest would work perfectly, but the main problem that I have is static column names. I want the names of the columns to be Q4 2023, Q3 2023 and so on in case of Quarter. This would change once we move to the next quarter. The same would be applicable for month and year.
How can I dynamically set the names of 5 columns that represents the past 5 periods? I’ve read about dynamic default of parameter and using it as a column name, but I want these dynamic values to be same for every user and we don’t have usernames or group names in our dataset.
Hi @iamtarun
you want or dont want dynamic columns?
if you want it should like this. i created two identical tables that only use a different parameter to demonstrate.
You can select the date(1/2) and the table shows the last 5 months.
Yes, I’m looking for what you’ve done in the attached image. I have a few questions regarding it.
What visual are you using? It looks like a Table, but I don’t understand how you’re able to get “stack_date”, and Jul 2020 above the column names.
Is it possible to not show the names such as “product_cost”, “stack_date” and only show Jul 2020 similarly for other columns as well.
I understand that the example you’ve shown is for “month” period. I assume if we want to do the same for Quarter, we’d get the first month in that quarter as the column name. For example, if the column belongs to 3rd quarter of the year, I assume the column name is going to be Aug 2020 as August is the first month in the 3rd quarter. Is it possible to set “Q3 2020” or some other string instead of Aug 2020.
Also, I would appreciate if you can tell me how you are achieved that visual. I’m fairly new to the Quicksight.
Thank you for helping me, I was able to get it working. But there’re two small issues.
First one is, since I’m using a Pivot Table, I’m viewing the first two columns as groups as you see in the Image 1. Is there a way to make it look like in the Image 2. In the Image 2, we can see “CA” is repeated multiple times unlike “AZ” from first image. I want the behavior similar to Image 1. I’m not to find the specific configuration to change it. How can we achieve this?
The second issue is, I created separate visuals for each of the periods just as you suggested. For the month and date, I used truncDate to get Month and Year respectively. This helps me in sorting the columns as well. But in case of Quarter, I’m using extract('Q', Date) to get the quarter number and concatenating with extract('YYYY', Date) with ‘Q’ as prefix. With this, I’m not able to sort the columns correctly. For example, I’m showing last 4 quarters from Q2 of 2023, that includes Q3 and Q4 of 2022 year, which would come first in the order if we sort in descending. I want to show the columns in descending order of the actual date I used while calculating the value for it (i.e. ex. Q1, 2023)
I just have one last question.
Is it possible to show the difference between the columns.
For example, from the image you shared, in the second visual, can we have a column that shows the difference between Q1’ 2020 and Q2’ 2020.
Yeah, I did check them out already. To work with periodOverPeriod* functions, we’d need to add “Date” column to the visual, which can be hidden. But the when I tried it, I’m getting some unexpected results. I’ll try it once again though.
One thing I noticed is, when I download the pivot table as CSV, the columns are not getting generated. I only see one column and the value for it is the value from the first column from the “stock_date” columns.
For example from your image, if I download the file, I’m only getting the values of Q1’ 2020 in the output, but not the rest.
But when I export the file to excel, it’s working fine. I’m able to see all the columns in it.
I understand that there is some limitation here from CSV and that’s why it’s not able to generate properly, but is there any hack/solution for it to work with CSV?
Were you able to find a solution or was @ErikG 's comment helpful? If so could you either post your solution or mark one of their comments above as a solution to help the community?
