Hello,
I am trying to calculate a Week over Week field, but I can’t do that. Could you, please, help me?
I’m using a pivot table with values (sales, revenue and cost) in rows and weeks in columns. What I want to do is put the week over week values in a column like in this table:
Thanks a lot in advance!
Hi there. Have you taken a look at this article: Period Over Period and Other Date Comparison Calculations (YoY, MoM, YTD, Rolling 7 days, etc)?
Hi, QuickSight just launched a suite of period-over-period functions. For your case, you just need to add a new calculated fied using this function. It should give you the right answer.
All other period functions are available in these two links: Period over period computation - Amazon QuickSight,
Period to date computation - Amazon QuickSight
FYI, we published this blog post for detailed discussion about use cases and function usage for the period functions.
Thanks Emily,
I actually ran into the same problem.
Follow up question: Is it possible to create exact same table like the excel example^. To be more specific, I want to make Date as the column, Revenue, cost as rows.
I look through all the examples in the articles you mentioned which all of the date is row instead of column.
And only show the one WOW at the end.
Hi @huangsn, do you have only two weeks? if you have more weeks how do you expect the result to show?
If you only have two weeks, you don’t need to use period function, you can directly add another calculated field using (sumif(measure, week=2)-sumif(measure, week=1))/sumif(measure, week=1)
Hi @emilyzhu!
Thanks for addressing the questions here and helping with the solutions.
I have a similar case as @huangsn but in my case the columns are weeks that dynamically change as we progress through the year. Example: At this moment, my dashboard displays columns in year-MonthNumber format. The columns are 2022-35, 2022-36, 2022-37, 2022-38. Next week, the columns will change to 2022-36, 2022-37, 2022-38, 2022-39.
Since the columns are dynamically changing every week, and I want to calculate the WoW for the two most recent weeks. How do you suggest doing that?
Hi @emilyzhu
Thanks for your help, similar to the WBR, I have 52 weeks data, and only want to display the two most recent weeks.
My use case is exactly same as @pravaljain explained ^.
@pravaljain and @huangsn, I now get your use cases. In this case, you would want to create a parameter to refer to the week you would like to calculate the WoW and use the function of something like (sumif(measure, week=$parameter)-sumif(measure, week=addDateTime(-1,'WK','$parameter))/sumif(measure, week=addDateTime(-1,'WK','$parameter)).
We recently launch another rolling date parameter feature. It may help to make it more dynamic and automatic. Check it out here.
Hi @huangsn and @pravaljain,
Were you able to add the ‘WoW’ column to the Pivot table? I am trying to build the same view, but I think creating a new calculated as Emily mentioned will not work, for example, if I created the WoW calculated field and add to the Pivot table in metrics, the new Pivot table will be like this?
Has this been solved? I have the exact same usecase where I don’t want to add an additional computation field because that will just create another row. I have different measures in the rows with dates going across. At the end I want to calculate the difference between the two most recent columns (ie one column with several different measures in each row all doing the difference in the date periods to the left).
There must be a solve for this, right??
Hello @danisear !
You should be able to use the pivot table functions to achieve what you are looking for.
If you have more questions on this function specifically or have a new question about QuickSight please feel free to post at the top of the community so that your question is a priority for the community experts!
