Metric level calculation

Hi team

Let’s say I have these accounts

Region   Country   Account
A        USA        abc
A        USA        def           
A        Canada     ghi
A        Canada     jkl

I’m creating an WBR view for revenue that will look like this on the analysis

Account - L4W - L3W - L2M - LM - QTD - YTD
abc - 4 - 5 - 10 - 11 - 20 - 50
def - 2 - 1 - 5 - 6 - 10 - 30

I want to be able to show YoY movement. Therefore, the columns of the dataset I currently have are like this

Account - CY_L4W - CY_L3W - CY_L2M - CY_LM - CY_QTD - CY_YTD - PY_L4W - PY_L3W - PY_L2M - PY_LM - PY_QTD - PY_YTD

Then, on QuickSight, I will have a YoY calculated field for each of the period. Example: YOY_L4W = CY_L4W/PY_L4W - 1. I

So I am able to show CY and YoY data but separately, meaning there are 2 ways I can do it

  • same table: Account - CY_L4W - YOY_L4W - CY_L3W - YOY_L3W. This is every messy and confusing to read. Also, there’s a limitation on the amount of columns
  • 2 tables: 1 shows CY and 1 shows YoY. This is a cleaner view and what I’m going with for now

But what I want is the ability to group these fields into a category (let’s call it Metric) so that I can show the data like this (pivot view)

Account   Metric  L4W   L3W  L2M  LM  QTD  YTD
abc       CY       4    5    10   11  20   50
          YoY      20%  15%       5%  3%   12%

Any idea on how I should do this (either on the dataset design or QS)? FYI, I can’t create the YoY columns on the dataset level because I wouldn’t be able to aggregate it to the country then region level. TIA

This looks like something that will need to be done via SQL.

Essentially you’ll have a row for CY and a row for YoY.

Or you’ll have rows for L4W - L3W - L2M - LM - QTD - YTD and then pivot the table.

You can create additional columns (calculated fields) in QS, but not rows which is what you are looking for in either case.

I don’t see how this could be done in QS.

Thank you Max for confirming my theory that this can’t be done via QS. I can’t do this in SQL because YoY will only be correct for account level but not for the country then region levels on QS. Unless you have some ideas?

Why would they not be correct for country and region levels?

Can you partition your calculations by these fields?

@luvis1189, please submit a PFR, we will evaluate the work and see if we can add to our roadmap next year. Thanks!