I understood how to calculate wow for one calculation with multiple categories. But if in this case below, each metric is a separate calculation. Can I create wow as a column dimension to replace the current wk/mo title? I am trying to avoid the way to create wow for each value in the screenshot below as I also need to create mom, rolling n wk/mo avg
Hello @yingkz !
If I understand correctly, do you want the GroupBy/Column of your pivot table to read like this:
01/January | 02/January | 03/January | 04/January
As of right now I don’t believe there is a truncDate / formatDate option to accomplish this, but a potential workaround could be to use the solution provided below. It will list the week number:
Hey @duncan I want the GroupBy/Column of pivot table only need the dimensions: WoW$, WoW%,Roling 3 wk avg
WoW$ WoW% Roling 3 wk avg
Beginning ATB
Net Claims
Cash Applied
…
Hello @yingkz !
For this use case, I think it would be better to use the table visual rather than the pivot table. Is there something from the table that you want specifically for your use case?
I tried both table and pivot table option, as this has multiple calculations/measures how can i create one single WoW$ dimension? I only can create WoW$ calculation for each measure.
WoW$_Beginning ATB
WoW$_Net Claims
WoW$_Cash Applied
Hello @yingkz, if you want to build a metric that will check only a single week, then you will likely need to use a few different calculations.
You can use an ifelse statement with the dateDiff function to check if the time between your date week field and now() is equal to 1 or 0, if so, return those dates, else NULL.
return2Weeks = ifelse(dateDiff({dateWeek}, truncDate('WK', now()) <= 1, {dateWeek}, NULL)
Then you can use the lag function to bring in the value associated with the previous week that you want to compare to the current week. Then aggregate the 2 values based on your expectations now that they exist on the same row.
Once you are done, when you use the field on your visual, you will want to filter the visual on the return2Weeks field to make sure you exclude NULLS so it only exists for this week and last week.
Your other option is to run the aggregation for WoW how you want it in your custom SQL statement and pull it into the dataset as a pre aggregated field value. That might be the easiest way to manage this so you don’t have to build work-arounds in QuickSight.
Hey @DylanM, let me clarify my question, my backend source table is mapping like the 1st table Backend Source Table below (not like the 2nd table below in Unpivot Table). Without unpivot table in backend sql, is there a way to create one function for WoW$ and another one for WOW% and have them in the same sheet? (I found I can use table across function to check the difference and different percentage in Quickight pivot table visual type but it still has to show WoW$ and WoW% in separate sheet.
My current state:
I used (currentvalue -lag function value) to create WoW$_metricA,WoW$_metricB and WoW$_metricC and put it in pivot table visual table. I create another sheet WoW%_metricA,WoW%_metricB and WoW%_metricC
Backend Source Table
Date MetricA MetricB MetricC
1/1 100 80 60
1/2 90 76 66
Unpivot Table
Date Metric
1/1 A
1/1 A
1/1 A
1/2 B
1/2 B
1/2 B
Hello @yingkz, it definitely complicates things with the dataset format, but I think it could be done, but it won’t be in a single calculated field for everything. I’ll do my best to explain.
You would need to create a date week calculated field:
dateWeek = truncDate('WK', {date})
Then, you would need a calculated field for last week’s value and another for this week’s value:
lastWeekMetricA = ifelse(dateDiff({dateWeek}, truncDate('WK', now(), 'WK') = 1, {metricA}, NULL)
lastWeekMetricB = ifelse(dateDiff({dateWeek}, truncDate('WK', now(), 'WK') = 1, {metricB}, NULL)
lastWeekMetricC = ifelse(dateDiff({dateWeek}, truncDate('WK', now(), 'WK') = 1, {metricC}, NULL)
Then you will need the same check to return the metric values for this week.
Once you are done, you can use the sumOver values to get the totals for each week and apply the calculations you want. It would look something like this:
(sumOver({currentWeekMetricA}, [], PRE_AGG) + sumOver({currentWeekMetricB}, [], PRE_AGG) + sumOver({currentWeekMetricC}, [], PRE_AGG)) - (sumOver({previousWeekMetricA}, [], PRE_AGG) + sumOver({previousWeekMetricB}, [], PRE_AGG) + sumOver({previousWeekMetricC}, [], PRE_AGG))
It definitely still takes a few calculated fields, but that would allow you to at least have a single calculation for your WoW field for all metrics.
One quick request. I know you have a few topics that are currently open to try and get your dashboard built out how you want that we have tried to assist with. To try and make this a little easier for us to assist you, can you try and take the questions you still need assistance with, and put them together in a new question topic in the community? That will help organize our assistance and make it easier to follow what you still have left to accomplish. Once you post that new topic, I will archive the old ones we have been responding to. Thank you very much!
@DylanM Thank you for the reply and I do not need to caculate WoW total for all metrics. I use ‘Add table calculation’ and select the difference of last two weeks for each metrics to avoid multi calculations. And for 6 month Avg, I create separate calculation field for each metrics.
So it seems like if I did not unpivot the data, there is no solution to create one single 6 month Avg calculation.
Here are all my questions and my current solutions for that: Quicksight questions
Thank you for taking your time to look into my questions