Hi team,
So I understand in order to make what is in the screenshot happen with rolling week numbers, I would need a parameter to accept user input, but how do I embed it in the calculated field. For example, if the user put wk5, then the table shows wk5 wk6 wk7 wk8… then Jan, Nov, MoM.
I have a column named “week” in which the values are like wk01,wk02…
and a "date"column in which the start date of each week is specified.
Hello @kanzhao, there are a few ways you can handle this depending on how your data is set up! One option is to set up a parameter controlled filter for a startDate. Create a datetime parameter where you can set your first date as the default or something like the beginning of the year. Build a control for the parameter where the user can select a date. Then use a custom filter for your date field, and base the start date off the parameter you created. That is likely the easiest solution.
Since your field of wk5, wk6, wk7 is a string field, you wouldn’t be able to check if the parameter value of wk5 is greater than or equal to something. The alternative would be to set a parameter value for the wk field, then use the substring() function. Something like this:
ifelse(parseInt(substring(${weekParam}, 3, 1)) <= parseInt(substring({weekField}, 3, 1)), {desiredValue}, NULL)
Then you can filter on that calculated field, select custom filter, and hit exclude nulls. I’ll mark this as a solution but if you have any follow-up questions, please let me know!
Hi Dylan,
that was super helpful, thank you!
Now I have something like this done, which is exactly what I want!
If I may bother u with some more questions, can I ask if there is a way to have the “April” and “May" columns dynamically changed according to its week selected?
I have the raw data in a format basically like this:
month week
January wk01
January wk02
January wk03
February wk05
February wk06
February wk07
February wk08.
And 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 you demonstrated earlier in the calculated field “${weekminus1}=concat(“wk”,toString(parseInt(substring(${weekParam},3,2))-1))” However, the parameters keep returning the default values
Hello @kanzhao, I apologize for my delayed response. Since we were able to answer your initial question, do you mind posting your last response as a new question in the community? That will ensure you are at the top of the priority list for a response from one of our QuickSight experts. Thank you!
Hi Dylan, thanks again! Will do that 
