Metric level calculation

Question & Answer
,
1

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

2

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.

3

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?

4

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

Can you partition your calculations by these fields?

5

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