Issues with using total on field

[Alex1p]

I have a query where i am trying to get the avg usage over the last 12 months. To get this i am using this code below

Code Select Expand

total([Bal Prin] for [Account Number]) / 12
This code is giving me the right total. My issue however, is when I run the query I am pulling 12 entries because I am pulling 12 months worth of data. I want to pull 1 entry but with the avg usage still accurate as of 12 months data. If you look at the example below I want to pull counter = 1 only but still have the avg usage stay at 158.33. I tried doing a filter on the counter = 1 and then set it to after auto aggregation thinking that would work ,but it drops the avg usage to 100 b/c it thinks it's the only value. Any help is appreciated!

For example
counter   Date    Balance   Avg Usage Balance(Add Balance all together and divide by 12)
1         1/1/23    100          158.33
2         2/1/23    100          158.33
3         3/1/23    100          158.33
4         4/1/23    100          158.33
5         5/1/23    100          158.33
6         6/1/23    200          158.33
7         7/1/23    200          158.33
8         8/1/23    200          158.33
9         9/1/23    200          158.33
10       10/1/23    200          158.33
11       11/1/23    200          158.33
12       12/1/23    200          158.33

[MFGF]

I have a query where i am trying to get the avg usage over the last 12 months. To get this i am using this code below

Code Select Expand

total([Bal Prin] for [Account Number]) / 12
This code is giving me the right total. My issue however, is when I run the query I am pulling 12 entries because I am pulling 12 months worth of data. I want to pull 1 entry but with the avg usage still accurate as of 12 months data. If you look at the example below I want to pull counter = 1 only but still have the avg usage stay at 158.33. I tried doing a filter on the counter = 1 and then set it to after auto aggregation thinking that would work ,but it drops the avg usage to 100 b/c it thinks it's the only value. Any help is appreciated!

For example
counter   Date    Balance   Avg Usage Balance(Add Balance all together and divide by 12)
1         1/1/23    100          158.33
2         2/1/23    100          158.33
3         3/1/23    100          158.33
4         4/1/23    100          158.33
5         5/1/23    100          158.33
6         6/1/23    200          158.33
7         7/1/23    200          158.33
8         8/1/23    200          158.33
9         9/1/23    200          158.33
10       10/1/23    200          158.33
11       11/1/23    200          158.33
12       12/1/23    200          158.33

Hi,

I assume you already have a filter that brings in only the last 12 months? When you say you want to "pull one entry", do you mean you want to see the specific date and balance, or do you just want the average balance without those being displayed? If the latter, just remove Date and Balance from being displayed in your list. Also, I'm not clear how Account Number plays into this. Is the query filtered for a specific account?

If you want to see details for a specific month, as well as the average usage for the last 12 months, you are going to need to do a little more work in your report. I'm assuming this is a relational model, not a dimensional one? If so, you will need a separate query in your report that calculates the average usage balance. Add this query (from the queries page), bring in the balance, filter it for the last 12 months (and possibly for the account), then add your calculation as at present. Also add a calculation to the query with a literal value of 1.

Then go back to your original query, and add a calculation to that one with a literal value of 1. On the Queries page, add a join based on that literal 1 calculation - so that every row in your original query joins to the row in your new query with your average in it.

In the query your join feeds in to, bring in all the items from your original query, plus the average from the new query. Base your list on this new query.

Cheers!

MF.

[Alex1p]

Hi,

I assume you already have a filter that brings in only the last 12 months? When you say you want to "pull one entry", do you mean you want to see the specific date and balance, or do you just want the average balance without those being displayed? If the latter, just remove Date and Balance from being displayed in your list. Also, I'm not clear how Account Number plays into this. Is the query filtered for a specific account?

If you want to see details for a specific month, as well as the average usage for the last 12 months, you are going to need to do a little more work in your report. I'm assuming this is a relational model, not a dimensional one? If so, you will need a separate query in your report that calculates the average usage balance. Add this query (from the queries page), bring in the balance, filter it for the last 12 months (and possibly for the account), then add your calculation as at present. Also add a calculation to the query with a literal value of 1.

Then go back to your original query, and add a calculation to that one with a literal value of 1. On the Queries page, add a join based on that literal 1 calculation - so that every row in your original query joins to the row in your new query with your average in it.

In the query your join feeds in to, bring in all the items from your original query, plus the average from the new query. Base your list on this new query.

Cheers!

MF.

Hi MF thanks for your response!

1. Yes i am using a filter to pull only 12 months worth of data.
2. I have a list of account numbers that have a balance through an entire year.
3. I don't need to see a balance for a specific month. just need to get the average balance for multiple account numbers.

To add a little more detail on how my report is currently setup.

I have my 1st query which contains acct numbers and a bunch of other fields such as owners name, current balance as of today,date opened, etc. No filters on this one.

My 2nd query is where i am trying to do my average balance. In this query i have a filter that is showing 12 months worth of balance information. I have fields like account number, balance, date(shows the date for the first of each month ex: 1/1,2/1,3/1,etc), a calculated field doing

Code Select Expand

total([Bal Prin] for [Account Number]) / 12 I wanted to do a left outer join with these 2 queries and join using the account number since those will match.

At the end I wanted a result that would show just 1 entry per account number with an avg balance attached.

Ex:
account number  owners name  current balance as of today      date opened    avg balance
123                              Alex            500                                         1/31/2024          158.33
545                              MF              1000                                        1/20/2024          600

[MFGF]

Hi MF thanks for your response!

1. Yes i am using a filter to pull only 12 months worth of data.
2. I have a list of account numbers that have a balance through an entire year.
3. I don't need to see a balance for a specific month. just need to get the average balance for multiple account numbers.

To add a little more detail on how my report is currently setup.

I have my 1st query which contains acct numbers and a bunch of other fields such as owners name, current balance as of today,date opened, etc. No filters on this one.

My 2nd query is where i am trying to do my average balance. In this query i have a filter that is showing 12 months worth of balance information. I have fields like account number, balance, date(shows the date for the first of each month ex: 1/1,2/1,3/1,etc), a calculated field doing

Code Select Expand

total([Bal Prin] for [Account Number]) / 12 I wanted to do a left outer join with these 2 queries and join using the account number since those will match.

At the end I wanted a result that would show just 1 entry per account number with an avg balance attached.

Ex:
account number  owners name  current balance as of today      date opened    avg balance
123                              Alex            500                                         1/31/2024          158.33
545                              MF              1000                                        1/20/2024          600

Hi,

I actually think you are there already. Just join the queries as you describe, and bring in the Avg Balance from the second query. You don't need to filter that second query any further - if you do, it will mess up the calculation. As long as you don't pull in the date from the second query, you should just get a single row for each Account Number.

Cheers!

MF.