I’m using this formula to populate WoW3:
{act_cy} - periodOverPeriodLastValue({act_cy}, {period_end_day}, WEEK, 1)
act_cy : aggregating field
period_end_day : weekend (Sat) date
period : ISO Week number of the week
I get values when I group by period_end_Day, but when I bring in period dimension, I don’t get values anymore, even though Period - period_end_day have 1:1 mapping.
How can I solve this, I need the WoW value at a period number level.
Hello @Ankit.Singh, welcome to the QuickSight community!
I want to clarify a few things regarding your expected output so I can guide you further on this. It would also be helpful if you were able to set up a demo version of what you are hoping to accomplish in a QuickSight Arena analysis so I could test it myself.
For the period_end_day version of the calculation, is it returning the last value for the saturday of the previous week? If that is the case, is the result you are expecting from ISO Week number to return the last value for the week even if there isn’t a value on Saturday? I just want to make sure I understand your expectations.
Would you be able to utilize a Week Date instead of an ISO Week date for the calculation? I can’t find any explicit limitations regarding that type of date field, but it does seem like the date type is likely the issue here.
Hi @DylanM , period_end_day is the Saturday date of the week,
so for period (iso week) of week 17 2024, period_end_day will be 4/27/2024.
For my use case, I need the period (ISO Week num) to appear as the “Columns” in my Pivot Table visual, I can’t show the ‘week end day’ in the columns, hence I have a limitation in this sense.
But as soon as I group by the period, the values vanish, and the visual only works when I have period_end_day in the grouping field.
PS: I’ve added a sample dash in the Arena QS_Arena_dash_1
Hello @Ankit.Singh, thank you for taking the time to put this in QuickSight Arena! That helps a lot trying to figure out the solution for this issue.
So in order to make this work, you can use the lag function instead of periodOverPeriodLast value to compare week by week. I tested this and this will work if you leave the period_end_day field in the visual, but you can hide it from the actual display. Then you can use this function to return the values how you are expecting:
{act_cy} - lag({act_cy}, [{period_end_day} ASC], 1)
That should give you the solution! Let me know if you have any remaining questions.
Hi Dylan, unfortunately the Pivot table is much complicated than what I have put in the Arena, it has multiple cuts and dimensions in the rows.
I have updated the Arena with the sample data QS_Arena_dash_1
and I’m attaching the final expectation sample below, I’m trying to achieve something like this which would give me WoW and MoM in a single row based on the ‘reporting_time_period’ value
Hello @Ankit.Singh, we can try a few things, but I think you honestly may just want to split this up into 2 pivot table or split the WoW and MoM into 2 rows. Adding the reporting_time_window dimension is likely going to overcomplicate this pivot table and be difficult to debug.
I can’t fully test this in Arena either, because only WEEKLY is returning for period time window, but this is my thought. We can add a calculated field to return the period_end_day value differently depending on the reporting_time_period value and try to use that in our lag function instead. Note, you will have to keep the original period_end_day field in your visual and hide it to use the calculated field.
Period End Day by Time Window = ifelse({reporting_time_window} = 'MONTHLY', truncDate('MM', {period_end_day}), {period_end_day})
Then you can try replacing the period_end_day field with the new field in your lag function:
WoW/MoM = {act_cy} - lag({act_cy}, [{Period End Day by Time Window} ASC], 1)
If you are running into a lot of aggregation errors, I genuinely think the best course of action would be to split this into 2 visuals or set the WoW and MoM into 2 different row values. Let me know if this helps!
