HELP CENTER

HELP CENTER

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Dojo
- :
- Using Domo
- :
- Beast Mode, ETL/Dataflow
- :
- Account Ledger Running Balance Beast Mode

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

3 weeks ago

3 weeks ago

Hi All,

I am having trouble coming up with. beast mode to create a running balance for an accoutn ledger. I have all of my charges and reciepts but i cannot create a running balance to show the correct totals. Any help with this would be much appreciated. I have atached a picture showing the card i am working with. As you can see all the balance column is doing is taking the charge or receipt total. Thank you very much!

David

Accepted Solutions

Highlighted

Jae Wilson

Check out my Domo Training YouTube Channel

**Say "Thanks" by clicking the heart in the post that helped you.

**Please mark the post that solves your problem by clicking on "Accept as Solution"

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

3 weeks ago

3 weeks ago

To troubleshoot this try to isolate where the error is.

sum(

sum(

(CASE when `Type`='Charge' then `Amount Paid` else 0 end)+

(CASE when `Type`='Receipt' then `Total Amount` else 0 end)*(-1)

) over (order by `Date Created` asc))

figure out does this do what you expect it to do?

sum(

(CASE when `Type`='Charge' then `Amount Paid` else 0 end)+

(CASE when `Type`='Receipt' then `Total Amount` else 0 end)*(-1)

)

I wouldn't expect it to... or at least, i believe you can simplify your case statement:

sum(

CASE

when `Type`='Charge' then `Amount Paid`

when `Type`='Receipt' then `Total Amount` * -1

else null

END

)

)

THEN Does this give the desired result?

sum( sum( `Amount Paid`) over (order by `Date Created` asc))

I wouldn't expect it to.

sum( sum( `Amount Paid`) ) over (order by `Date Created` asc)

Note how the parenthesis moved.

Jae Wilson

Check out my Domo Training YouTube Channel

**Say "Thanks" by clicking the heart in the post that helped you.

**Please mark the post that solves your problem by clicking on "Accept as Solution"

All Replies

Highlighted
##

**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

3 weeks ago

3 weeks ago

Re: Account Ledger Running Balance Beast Mode

You'll want to do this utilizing an ETL. It's possible to do it in a Beast Mode with window functions however it would start on the first date that is filtered and not going back entirely in time to the start of your ledger. For example if you only displayed this year the beast mode variant would have $0 to start the year rather than the carry over from last year.

Here's a prior solution to using MySQL ETL to get a running total you could look at.

https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/Running-total-ETL-or-MySql/m-p/39593

**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**

Highlighted
##

Jae Wilson

Check out my Domo Training YouTube Channel

**Say "Thanks" by clicking the heart in the post that helped you.

**Please mark the post that solves your problem by clicking on "Accept as Solution"

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

3 weeks ago

3 weeks ago

Re: Account Ledger Running Balance Beast Mode

You can accomplis this window functions if you have them enabled in your instance, you may have to ask your CSM to 'enable window functions in beast modes'

try the following beast mode:

sum(sum(amount) over (order by date_column asc)

just swap in the correct column names

To overcome the ledger problem APPEND a snapshot row to your dataset that shows the lifetime balance at the start of the year.

Jae Wilson

Check out my Domo Training YouTube Channel

**Say "Thanks" by clicking the heart in the post that helped you.

**Please mark the post that solves your problem by clicking on "Accept as Solution"

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

3 weeks ago

3 weeks ago

Re: Account Ledger Running Balance Beast Mode

Hi jae,

I will reach out to our CSM to see about window functions.

For your sum beast mode how would it look whem i am trying to sum (Charge) + (Receipt) on some of the line items. It not always just summing charges or receipts. Thanks

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

3 weeks ago

3 weeks ago

Re: Account Ledger Running Balance Beast Mode

I tried this:

sum(sum((CASE when `Type`='Charge' then `Amount Paid` else 0 end)+(CASE when `Type`='Receipt' then `Total Amount` else 0 end)*(-1)) over (order by `Date Created` asc))

It was valid but my card is now not loading so i rposbably did something wrong.

Highlighted

Jae Wilson

Check out my Domo Training YouTube Channel

**Say "Thanks" by clicking the heart in the post that helped you.

**Please mark the post that solves your problem by clicking on "Accept as Solution"

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

3 weeks ago

3 weeks ago

To troubleshoot this try to isolate where the error is.

sum(

sum(

(CASE when `Type`='Charge' then `Amount Paid` else 0 end)+

(CASE when `Type`='Receipt' then `Total Amount` else 0 end)*(-1)

) over (order by `Date Created` asc))

figure out does this do what you expect it to do?

sum(

(CASE when `Type`='Charge' then `Amount Paid` else 0 end)+

(CASE when `Type`='Receipt' then `Total Amount` else 0 end)*(-1)

)

I wouldn't expect it to... or at least, i believe you can simplify your case statement:

sum(

CASE

when `Type`='Charge' then `Amount Paid`

when `Type`='Receipt' then `Total Amount` * -1

else null

END

)

)

THEN Does this give the desired result?

sum( sum( `Amount Paid`) over (order by `Date Created` asc))

I wouldn't expect it to.

sum( sum( `Amount Paid`) ) over (order by `Date Created` asc)

Note how the parenthesis moved.

Jae Wilson

Check out my Domo Training YouTube Channel

**Say "Thanks" by clicking the heart in the post that helped you.

**Please mark the post that solves your problem by clicking on "Accept as Solution"

Announcements