Difference of % of column total

Chayan · 2023-11-19T16:58:05+00:00

There was an error rendering this rich post.

I have two column where I use % of column total to calculate the Share of Markets now I wish to get the difference of the percentages for example 31.91% - 31.10% will give me 0.81% difference. I just cant simply do that, any Idea how to implement this?

Find more posts tagged with

Accepted answers

GrantSmith

Ah that makes sense now.

You'll likely need to use a different beast mode to calculate the percent of total using something called a window function. A window function will return an aggregation across your entire dataset or subsection of data.

Current Year

SUM(CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE) THEN `Value` END) / SUM(SUM(CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE) THEN `Value` END)) OVER ()

Last Year

SUM(CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE)-1 THEN `Value` END) / SUM(SUM(CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE)-1 THEN `Value` END)) OVER ()

Difference:

(SUM(CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE) THEN `Value` END) / SUM(SUM(CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE) THEN `Value` END)) OVER ())
-
(SUM(CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE)-1 THEN `Value` END) / SUM(SUM(CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE)-1 THEN `Value` END)) OVER ())

All comments

ColemenWilson

You can create a beastmode (calculated field) like this:

SUM(YTD_22) - SUM(YTD_23)

Then drag the beastmode field into your table and it will show the difference between the two columns in your screenshot.

GrantSmith

The concept is the same if you're using beast modes to calculate the YTD_22 and YTD_23 values, just create a new beast mode by subtracting your 22 beast mode from 23 beast mode as @ColemenWilson mentioned.

Chayan

here's the thing see the YTD_22 column is '% of column total' see below

Now I wish to see the difference just by SUM(YTD_22) - SUM(YTD_23) is not working because I'm using % of column total to get a share of market. In this case, this formula doesn't work, any other way around?

Chayan

ColemenWilson, @GrantSmith. Any idea how to resolve this? any help will be highly appreciated! thanks in advance.

GrantSmith

Ah that makes sense now.

Current Year

SUM(CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE) THEN `Value` END) / SUM(SUM(CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE) THEN `Value` END)) OVER ()

Last Year

SUM(CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE)-1 THEN `Value` END) / SUM(SUM(CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE)-1 THEN `Value` END)) OVER ()

Difference:

(SUM(CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE) THEN `Value` END) / SUM(SUM(CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE) THEN `Value` END)) OVER ())
-
(SUM(CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE)-1 THEN `Value` END) / SUM(SUM(CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE)-1 THEN `Value` END)) OVER ())