periodOverPeriodPercentDifference not working when weeknum is in visual

[Phillip_Le]

My Traffic WoW field works in the visual, but once I add the weeknumber field, it doesn’t populate for some reason? I have been trying to debug but haven’t found the issue, but for some reason, the Traffic YoY still works?

Not working when adding weeknumber

Working without weeknumber

Datefield (calculated field) -

ifelse(
   ${Periodstarting}='Day', truncDate("DD", {hit_day_loc}),
   ifelse(${Periodstarting}='Month', truncDate("MM", {hit_day_loc}),
      ifelse(${Periodstarting}='Week', addDateTime(6, "DD", truncDate("WK", {hit_day_loc})),
         truncDate("YYYY", {hit_day_loc})
      )
   )
)

weeknumber (calculated field)

ifelse(
    ${Periodstarting} = 'Week',
    weeknum(Datefield),
    NULL
)

Traffic WoW (calculated field)

ifelse(
    ${Periodstarting} = 'Week',
    periodOverPeriodPercentDifference(
        sum(traffic),
        Datefield,
        WEEK,
        1
    ),
    NULL
)

Traffic YoY (calculated field)

ifelse(
    ${Periodstarting} = 'Week',
    periodOverPeriodPercentDifference(sum(traffic), Datefield, WEEK, 52),
    ifelse(
        ${Periodstarting} = 'Month',
        periodOverPeriodPercentDifference(sum(traffic), Datefield, YEAR, 1),
        ifelse(
            ${Periodstarting} = 'Year',
            periodOverPeriodPercentDifference(sum(traffic), Datefield, YEAR, 1),
            NULL
        )
    )
)

[apjvinod]

Hi @Phillip_Le

Let me research further on this , it appears to be issue and also weeknum is not quickSight supported function in documentation .

I would suggest you can change the Datefield format to include the weeknumber instead of showing it as separate column .

In the field well choose Datefield–Format–More Formatting Options–Custom MMM D, YYYY (W)

[Phillip_Le]

I think this is a good alternative solution, but when period, month, and year are chosen, then it becomes funky.

[WLS-Luis]

Hi @Phillip_Le,

Just wanted to check in with you since I saw you have not received a response in a while. Were you able to find a workaround to your problem? If not, could you provide some clarification on the result of running apjvinod’s alternative solution? If we do not receive a response within 3 business days, I’ll close this topic.

[WLS-Luis]

Hi @Phillip_Le,

Since we have not heard back, I will close out of this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!