How can I query my data to flip my rows and columns?

How can I transform my data so the years go from rows to columns and my metrics go from columns to rows?

There are two methods to accomplish this: using C9QL or using self serve analytics (SSA), both will follow the same three steps.

Step 1: Use the reverse transpose function to turn your metric columns into rows. The function syntax is REVERSE_TRANSPOSE(<Name of the new column>, < name of the new value column>, <list each column you want to turn into rows>). In this instance we would keep the year column selected and the C9QL would look like this: 

Select Year, REVERSE_TRANSPOSE(metric, value, Count of Sales, Sales Revenue, COGS, Profit Margin); 

To do this using SSA you would drag the Year field into fields/metrics, and use the +Add Function button to add your reverse transpose function. The resulting query will look like this:

Step 2: If you are using the C9QL method don't forget the semicolon after the last step to indicate we are adding a second step. In the SSA method click the + Add Step button in the top right. In this step we will turn the Year row into individual columns for each year using the Transpose function. The syntax for this function is Transpose(<key field to transpose>, <value field>). In this case those fields are Year, and Value (This field was created in the previous step), also make sure to select the Metric field (also created in step 1). The C9QL function will look like this: 

select metric, TRANSPOSE(Year, Value);

In SSA drag Metric into fields/metrics and add your transpose function using +Add Function.

Step 3: The final step is to group all the data by metric. Make sure to include your semi colon or click +Add Step from the steps drop down like we did in step 2. The C9QL for this step is:

select metric, 2021, 2020, group by metric

In SSA drag the All field into fields/metrics, and the metric field into grouping/dimensions

Your final query should look like this: