WoW Calculations in Pivot Table -> blank column at the last Week

Question & Answer
1

I want to have the last column of this view also calculate WoW % changes but without needing to pull in the prior week’s (Dec 18th) hours. I’m aware of the “Date Hider” method, but I want the date (Dec 25) also visible with it’s own WoW calculation (even though Dec 18th is invisible/filtered out).

WoW View
WoW View612×548 67 KB

2

Unfortunately to have a calculation that references a prior week you need to not have filtered that week out.

If you moved this logic to SQL and have a WoW % Change in your dataset instead of a calculated field that should work.

1 Like
3

image
do you have any suggestions for how I could create this in SQL (creating the yellow WoW column in SQL)? Here’s example of dataset

SELECT
a.Process_Path
,a.Site
,a.Metric1
,a.Metric2
,a.Period
,a.Balance_Date

from TABLE

4

Yep you can use a lag function! Depending on your sql technology here is how it might look.

SELECT
a.Process_Path
,a.Site
,a.Metric1
,a.Metric2
,a.Period
,a.Balance_Date
,(a.Metric2 - lag(a.Metric2) OVER (PARTITION BY a.Process_Path, a.Site, a.Metric1, a.Period ORDER BY a.Balance_Date ASC)) / lag(a.Metric2) OVER (PARTITION BY a.Process_Path, a.Site, a.Metric1,a.Period ORDER BY a.Balance_Date ASC) week_over_week

from TABLE

Let me know if that helps!

5

I’m running into errors with it, I think it’s because the version of MySQL I have is 5.6.10-log and not 8.0 version (which allows for LAG) → any other suggestions?

sourceErrorMessage:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘OVER (PARTITION BY a.Process_Path, a.Site, a.Metric1, a.Period ORDER’ at line 10

6

I would suggest upgrading your MySQL.

Besides that you can look to this.

7

Would the LAG Function within QS on the dataset calculations (adding a new column) be a workaround?

8

You can try that but, it might filter out the days you want before.

Let me know if it works for you.

9

Hi @Clayton_Barton
Did any of Max’s solutions work for you? I am marking his reply about using Lag functions either at the database (MySQL) or QS data prep layer as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community Q&A Forum!