By using this website, you agree to our Terms of Use (click here)
I made a cash by period inquiry to ultimately turn into a chart widget. However, in this inquiry, each period is driven from a transaction (GLTran) occurring for each account, so, for example, our 10010 account never had a transaction in July, so it does not appear in this inquiry even though it has cash in it from previous periods. Another issue with this is that in the beginning of months, like currently, we might not have any transactions at the moment, so it shows as zero cash in the widget. I was wondering if there is a way to use the ending balance of accounts that have no transactions of the previous month and keep that going for future periods, since that is the true cash value in that account even if there are no transactions for that period. Or maybe there is a better source for me to use?
Hi Jake,
Not exactly sure what you're looking for here. Could you create a few lines of sample data in Excel, then create a chart in Excel that illustrates what you are trying to create? The you can attach it back here and I'll take a look at it.
The data on the left is from the account by period project, and it does not let me call to its Data class directly, so I am using GLHistory. The data in the middle is an example of how I would have it set up being sorted by account #, but that is not important. What I am really trying to accomplish is having the line chart widget show an accurate depiction of the current period. It currently does not include one of my accounts for Aug because, as you'll read in my comment in box C17, there is no data for that account even though there was an ending balance for the previous period.
The trick here is going to be getting your joins right.
I was first thinking to do this:
1. Start with the Account table and make sure to populate the Conditions tab with the specific accounts that you want (eg. 10000 and 10010).
2. Try a Cross join to FinPeriod. This should give you a record for every financial period.
3. Try a join from Account to GLHistory on Account.AccountID=GLHistory.AccountID.
But then I realized that you still won't get a value, even though it solves the problem of not having a row.
So, I'm stumped on this one, without writing a SQL View or using Power BI. There might be a way to do it. I just can't think of one right now.
ok so I'm 7 years late to this, and maybe there's a better way now, but just in case anyone else is searching for this, I want to reply back. I ended up doing this, hope it makes sense, if not, I can explain further.
