Week on week variance along with week number label

Q&A
, ,
1

Hi All,

I have a quicksight table, where i get the WoW whenever i aggregate ticket_created_at by week, but as soon as i add the weekno column, WoW stops working and everything shows up as blank. Even if i try to hide ticket_created_at, it does not seem to work.

image (6)
image (6)1656×778 36.1 KB

Calculated field for weekno : concat(right(toString(extract("YYYY", {ticket_created_at})), 2), "-WK", toString(dateDiff(truncDate("WK", truncDate('YYYY', {ticket_created_at})), {ticket_created_at}, 'WK')+1))
Calculated field for WoW (periodOverPeriodPercentDifference(count({ticket_id}), {ticket_created_at}, WEEK, 1))*100.0

Could someone please help with this?
Thanks!

2

When i aggregate week wise ticket_created_at

image (5)
image (5)1666×780 37.4 KB

3

Hi @deepigrg,
This is interesting, as your not using ‘weekno’ in the calculation for WoW, its pretty odd that it’s creating blank cells across the board. I don’t have a similar dataset to test this off of so this could be a lot easier to assist if you’re able to upload a sample of your dataset/analysis to Quick Sight Arena, I’ll include the step by step process on how to create and share:

4

Hi @Brett
I would not be able to send across the dataset but I am adding a screenshot of mocked data
Please let me know if you can help. Thanks!

image
image2502×762 72.4 KB

5 
The week no has been calculated using the formula:Calculated field for weekno : concat(right(toString(extract("YYYY", {ticket_created_at})), 2), "-WK", toString(dateDiff(truncDate("WK", truncDate('YYYY', {ticket_created_at})), {ticket_created_at}, 'WK')+1))Calculated field for WoW (periodOverPeriodPercentDifference(count({ticket_id}), {ticket_created_at}, WEEK, 1))*100.0

@Brett would you know where I’m going wrong here

6

Hi @deepigrg,

Adding a field to your visual should not have an affect on your values unless it’s involved in the calculated field that creates those values (which in your case, it’s not utilized).

One thing I noticed from your screenshots though, based on the standard titles they have in place, it’s showing that when you add the ‘week no’ field, the end of the title changes to ‘by Ticket_created_at and Weekno’. So it sounds like you placed ‘Weekno’ into the group by section of your visual. So when the calculated field runs, it’s trying to incorporate all of your group by fields and therefore is returning blank values.

While I know the reason it’s leading to the blank cells, I’m still looking for alternate ways you can achieve while still included the weekno field since you can’t use PRE_AGG with a periodOverperiod function to try and ignore the weekno field in your grouping.

7

Hi @deepigrg

It’s been a while since we last heard from you. If you have any further questions, please let us know how we can assist you.

If we don’t hear back within the next 3 business days, we’ll proceed with close/archive this topic.

Thank you!

8

Hi @deepigrg

Since we have not heard back from you, I’ll go ahead and close/archive this topic. However, if you have any additional questions, feel free to create a new topic in the community and link this discussion for relevant information.

Thank you!