How to define a % achieved Calculated Column

1

Hello all

I'm trying to replicate a spreadsheet report.

Screenshot from 2023-04-12 14-04-11

It has a % Collected column which is a formula, e.g.on the second row, YTD 1902453 / 7148887 is 27%. I can Summarise the Amount over the Months to get the YTD total per row, but how do I achieve the % Collected, which is the row total / grand total?

Thanks so much.

2

have you checked Custom expressions in the notebook editor?

3

Hi @Luiggi

Yes, most certainly! However my problem is I do not know how to get hold of the YTD grand total. I can reference each row total as [YTD], but how do I reference the grand total in the expression?

4

Furthermore, I can define a custom Metric which is the sum of all the amounts which evaluates to 7148887, ie the grand total. However this Metric is not available in the Custom Column expression editor. Note that my custom Segments are available though :hot_face: I'm on v0.46

5

Can't you create a new question in which you create the grand total and then join it to the question that has the row totals ... then in the custom column you can use both the row total (by adding the columns) and dividing by the grand total you just joined?

1 Like
6

Thanks so much @TonyC

I guess that's a possible solution. I ended up coding it as a native SQL query

SELECT
    `job4_data_payment`.`Client Group` AS `Client Group`,
    `job4_data_payment`.`Book Name` AS `Book Name`,
    SUM(`job4_data_payment`.`Amount Received`) AS `Amount Received`,
    -- Use the OVER clause to modify a SUM scope in your query. No GROUP BY needed
    (SUM(`job4_data_payment`.`Amount Received`) / SUM(SUM(`job4_data_payment`.`Amount Received`)) OVER () * 100) AS `Percentage`
FROM `job4_data_payment`
GROUP BY `Book Name`
1 Like
7

Sometimes SQL is the best option for complex queries!

1 Like