Category
Report Builder
Product Version
7.3
Calculate difference between two dimensions as a column?
Answered
Is it possible to calculate the difference between a single metric across two dimensions? I.e. if we have a raw dataset that has two fields (1) country (2) revenue. If I create a tabular report with the data I would drag country dimension and revenue metric to the rows section to produce something like:
Now I don't want to create a new row for differences between US and UK, but rather a new column. Simple example:
Is this possible? Everything I try seems to want to create a new metric and hence a new row
The same question
HI Matt,
The best way to do this will be to use either calculated fields or sub-queries with filters to split your revenue column, so that you can create subsequent calculated fields for the difference.
For calculated fieds you would do something like
In this case if it is not the country for that field, the value will be null and will not count when you do a sum aggregation.
If you were to use sub-queries, you would need another column to join the data on, and then add filters in each sub-query to limit the data specifically to one country.
Let me know if this makes sense.
Regards,
Nathan
HI Matt,
The best way to do this will be to use either calculated fields or sub-queries with filters to split your revenue column, so that you can create subsequent calculated fields for the difference.
For calculated fieds you would do something like
In this case if it is not the country for that field, the value will be null and will not count when you do a sum aggregation.
If you were to use sub-queries, you would need another column to join the data on, and then add filters in each sub-query to limit the data specifically to one country.
Let me know if this makes sense.
Regards,
Nathan
That makes sense, I was just trying to avoid going down that route when I have 20+ metrics. My actual scenario is a little more complicated as I need to compare yesterday's data against prior day, same day last week and a rolling 7 day average. And there are 29 metrics I'm comparing.
Solution I went with is to create a column for each date period (so there are 4) and then a row for each metric. It leaves me with a "metric" dimension as rows and as a result I can't format those metrics appropriately
That makes sense, I was just trying to avoid going down that route when I have 20+ metrics. My actual scenario is a little more complicated as I need to compare yesterday's data against prior day, same day last week and a rolling 7 day average. And there are 29 metrics I'm comparing.
Solution I went with is to create a column for each date period (so there are 4) and then a row for each metric. It leaves me with a "metric" dimension as rows and as a result I can't format those metrics appropriately
Hi Matt
That does sound like the better route here.
Glad you got it working.
Regards,
Nathan
Hi Matt
That does sound like the better route here.
Glad you got it working.
Regards,
Nathan
Replies have been locked on this page!