How to add dynamic header for a table

Question & Answer
, ,
1

Hi team,

I have a use case where I need to have dynamic headers in a table. Specifically, I need the last 5 weeks to automatically get updated with the new week as time progresses.

For example, I need to have the last 5 weeks displayed in the columns, along with their corresponding week numbers. I know that this is possible to do using parameters, but I’m finding it challenging to implement based on the documentation.

Could you please provide some guidance or suggestions on how I can achieve this dynamic header functionality in the table.

image

Is there any workaround for this?

1 Like
2

Hello @cijod, after some testing, it looks like we can put this together with 2 calculated fields. First, you will want to build a calculated field that will create the week number field. This will also be a good place to filter out week number values we do not need.

Week Num =

ifelse(
addDateTime(-5, 'WK', now()) <= {Date field} AND now() >= {Date field},
dateDiff(truncDate("WK", truncDate('YYYY', {Order Date})), {Order Date}, 'WK')+1,
NULL
)

This will return the week number for the current week and previous 5.

Now, you can build a calculated field to return groups (use in the columns field well in a pivot table) that will be dynamic based on the current week number:

Week Num Category =

ifelse(
    maxOver({Week Num}, [], PRE_AGG) = {Week Num}, concat('Week ', toString({Week Num})),
    maxOver({Week Num}, [], PRE_AGG) - 1 = {Week Num}, concat('Week ', toString({Week Num})),
    maxOver({Week Num}, [], PRE_AGG) - 2 = {Week Num}, concat('Week ', toString({Week Num})),
    maxOver({Week Num}, [], PRE_AGG) - 3 = {Week Num}, concat('Week ', toString({Week Num})),
    maxOver({Week Num}, [], PRE_AGG) - 4 = {Week Num}, concat('Week ', toString({Week Num})),
NULL
)

Using the max value across your week numbers, you can check if it is equal to the last 5 weeks. This will update as each new week is created in the year. I’ll include some documentation below on maxOver, addDateTime, and concat functions.

This should return your desired result. I will mark this as the solution, but please let me know if you have any remaining questions. Thank you!

1 Like