How to display month over month change with dynamic week parameter

Question & Answer
, ,
1

qssnippet
qssnippet1489×763 118 KB

Hi team,

I have built a table with parameter input to display week over week change, and may I ask if there is a way to have the “April” and “May" columns dynamically changed according to its week selected?

I want the user to only select the week number, and then the table should include the month of the selected week and its previous month with month over month change.

I have the raw data in a format basically like this:

month week date
January wk01 2024/1/1
January wk02 2024/1/8
January wk03 2024/1/15

Additionally, for the columns “-1”, “-2”, “-3” these are meant for wk16 wk15. I tried creating parameter for each of these 3 column and connect them to calculated fields to pass the values in. I am using something like this in the calculated field “${weekminus1}=concat(“wk”,toString(parseInt(substring(${weekParam},3,2))-1))” However, the parameters keep returning the default values

2

Hello @kanzhao !

For the first part, where week # selection changes the Month choice, you should be able to use relevant values based on how your dataset example is set up.
You can check out more about that here >> Using a control with a parameter in Amazon QuickSight - Amazon QuickSight.
That being said, your comparison month filter may need something different. Do you want the comparison month to always be the month previous to the current month filter choice?

Finally, for your calculated field, what do you mean by it only applies to wk 16 and wk 15? Should no other rows have data of those columns?

2 Likes
3

Hi Duncan, thanks for your reply!

And I do apologize for the lack of context here, so I have 3 parameters for this table: “weekParam” , “ComparisionMonth” and “CurrentMonth”.

For the columns in the table, I am using calculated fields like this

“ifelse(parseInt(substring(${weekParam}, 3, 2)) = parseInt(substring(week,3,2)),rph,NULL)”
for the selected week’s RPH data(“wk17” column)

ifelse(parseInt(substring(${weekParam}, 3, 2)) -1 = parseInt(substring(week,3,2)),rph,NULL)
for the previous week RPH (“-1” column in the screentshot)

ifelse(parseInt(substring(${weekParam}, 3, 2)) -2 = parseInt(substring(week,3,2)),rph,NULL)
for the week prior to previous week (“-2” column)

So I would like to have the column names of “-1”, “-2” dynamically change with the selected week.

I understand you can insert parameter into the column name, and thats exactly what I did with column “wk17”, I put in “weekParam” parameter into the column name. But for other week columns, instead of “-1”, “-2”, how do I display “wk16”, “wk15” if the selected week is wk17?

And I do want the comparison month always be the month previous to the current month.

For month columns, my calculated fields are like this

ifelse(${CurrentMonth}=Month,rph,NULL)
ifelse(${ComparisionMonth}=Month,rph,NULL)

I am thinking if there is a way to have only 1 parameter(weekParam) for the whole table, and the table could shift all the columns including the months?

4

Hi team, is there an update on this? Thank you

5

Sorry again, just wanted to check if there is any solutions for this? Really appreciate if anyone could share some insight

6

Just coming back to see if there is any solutions to this?

7

Hey @kanzhao ,

so there is one ‘crazy’ solution for your case. For -1/-2/-3 and April and May you could create tables which use specific ifelse statement to print your desired outcome (e. g. ifelse(parameter=‘wk17’, ‘wk16’, etc.) and then try to lay them over as the title.
It would look loke thtis:
image
And I would recommend to do it for all the header so the look similar. It’s a lot of work but could do the trick.
If I have test data i could try to create something similar in QuickSight Arena.
Feel free to ask any questions, it’s a time intensive solution and maybe i didn’t explained it quit well.

BR
Robert

3 Likes