Beast mode: SUM BY ORG NAME the AMOUNT if the state is granted or closed

AngelaO415
AngelaO415 Member
in Beast Mode

Hi: Can anyone help with Beast Mode code:

For each organizations_name, I want to get the sum of requests_amount_recommended if requests_state is granted or closed.

organizations_name

requests_state

requests_amount_recommended

org A

pending_review

10,000.00

org A

closed

10,000.00

org A

closed

75,000.00

org A

closed

100,000.00

org A

granted

180,000.00

org A

closed

16,634.00

org A

rejected

12,600.00

org A

closed

12,600.00

org B

closed

3,378.00

org B

granted

4,810.00

org C

closed

100,000.00

org D

pending_review

25,000.00

org E

rejected

0.00

org F

closed

5,000.00

org F

granted

4,480.00

org G

closed

400,000.00

org G

closed

400,000.00

org G

closed

400,000.00

org G

pending_review

100,000.00

Best Answer

  • david_cunningham
    david_cunningham Coach
    Answer ✓

    @AngelaO415 sure, here you go.

    SUM(SUM(case when requests_state in ('granted','closed') then requests_amount_recommended end)) over (partition by organizations_name)
    image.png

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

Answers

  • david_cunningham
    david_cunningham Coach

    @AngelaO415 - here you go. You would use this beast mode as the input to your y-axis if you are using a bar chart.

    case when requests_state in ('granted','closed') then requests_amount_recommended end
    image.png

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • AngelaO415
    AngelaO415 Member

    Hi @david_cunningham - What would the code be if it's in a table? I used your code but it's not summing by org_name

    Screen Shot 2024-05-09 at 7.48.32 PM.png

  • david_cunningham
    david_cunningham Coach
    edited May 10

    @AngelaO415 are you summing by both requests_amount_recommended and your new beast mode? The same beast mode will work in a table.

    I can't tell from your screenshot what's really going on. But in the below example, I'm using a table, and you can see that there is no repeat organization_name, and both fields are summed.

    image.png

    Happy to help out further, but will need a bit more info about what you're seeing 😁

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • AngelaO415
    AngelaO415 Member

    Is there a way to keep the repeating rows of org names and just have a column that does the SUM IF part. I need the rows to repeat for other data purposes. I did this via Excel but Beast Mode is not my jam. The last column "desired output" is what'd I'd like to see:

    organizations_name

    requests_state

    requests_amount_recommended

    desired output

    org A

    pending_review

    10,000.00

    $185,000.00

    org A

    closed

    10,000.00

    $185,000.00

    org A

    closed

    75,000.00

    $185,000.00

    org A

    closed

    100,000.00

    $185,000.00

  • david_cunningham
    david_cunningham Coach
    Answer ✓

    @AngelaO415 sure, here you go.

    SUM(SUM(case when requests_state in ('granted','closed') then requests_amount_recommended end)) over (partition by organizations_name)
    image.png

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • AngelaO415
    AngelaO415 Member

    Thanks so much @david_cunningham. It worked! You're such a pro!

  • AngelaO415
    AngelaO415 Member

    Hey @david_cunningham - How would I edit your latest code if in the dataset, the request IDs repeat:

    request_transactions_organization_payee_id= organization

    Base Request ID = application

    request_amount_recommended

    My goal is to get the grant amount (request_amount_recommended) by payee org for each distinct request ID.

    request_transactions_organization_payee_id

    Base Request ID

    request_amount_recommended

    requests_state

    desired output - total amount to payee org

    8

    2022-2018131

    $603,750.00

    granted

    $3,603,299.00

    8

    2022-2018131

    $603,750.00

    granted

    $3,603,299.00

    8

    2022-2018131

    $603,750.00

    granted

    $3,603,299.00

    8

    2019-2016363

    $609,549.00

    closed

    $3,603,299.00

    8

    2019-2016363

    $609,549.00

    closed

    $3,603,299.00

    8

    2019-2016363

    $609,549.00

    closed

    $3,603,299.00

    8

    2019-2016363

    $609,549.00

    closed

    $3,603,299.00

    8

    2019-2016315

    $300,000.00

    closed

    $3,603,299.00

    8

    2019-2016309

    $20,000.00

    closed

    $3,603,299.00

    8

    2019-2016310

    $600,000.00

    closed

    $3,603,299.00

    8

    2022-2018151

    $50,000.00

    closed

    $3,603,299.00

    8

    2022-2018156

    $300,000.00

    closed

    $3,603,299.00

    8

    2018-2016128

    $250,000.00

    closed

    $3,603,299.00

    8

    2018-2016128

    $250,000.00

    closed

    $3,603,299.00

    8

    2018-2016283

    $250,000.00

    granted

    $3,603,299.00

    8

    2018-2015885

    $400,000.00

    closed

    $3,603,299.00

    8

    2018-2015886

    $20,000.00

    closed

    $3,603,299.00

    8

    2018-2015890

    $200,000.00

    closed

    $3,603,299.00

    8

    2017-2015502

    $200,000.00

    pending_review

    $3,603,299.00

    8

    2017-2015518

    $400,000.00

    pending_review

    $3,603,299.00

Categories