Average ignore columns

[#1]

I hope someone can help me with this:

I'm trying to get an average of exchange rates for a particular calendar selection. This is calculating fine, however, when i group the transactional data by account category, the AVG(ExRate) does not give the average for months where there are no transactions. Is there a way for the calculation to ignore the account category grouping?

Let me know if I need to send a sample QVW.

thanks

James

[kaushiknsolanki]

HI,

     Its better that you create an inline table with Month and the numbers of days in month and then use this in calculation to compute the avg.

Regards,

Kaushik Solanki

[daveamz]

Hi James,

You can use something like sum(Values)/count(DISTINCT Field), where Field contains the months.

Is there no way to calculate an avg for null data. If you have the values in the same table with the month information you can transform the the null values in the script with if(isnull(ExRate),0,ExRate) as ExRate in order to calculate the avg in chart.

Regards,

David

[#4]

Thanks Kaushik

Maybe you can help me 'plug it in' - here is what I have (see screen shot)

thanks a mil

James

[#5]

Thanks for the response David.

I dont think the 'count() option is a possibility, because the AVG calculation would still only include the months that the account categories exist in the transactions. (see the image i posted to the previous response)

[kaushiknsolanki]

Hi,

     Just one thing tell me on what fields you have linked them together.

Regards,

Kaushik Solanki

[#7]

sorry - meant to say, I have joined the tables on MONTH and YEAR.

[kaushiknsolanki]

Hi,

     You can do two things.

     1. Enter the records for the March month in the First Table with 0 amount.

     2. You join them and make one table.

     In second option you will have to avg the exchange rate first and then link them, other wise data will be replicated many a times.

Regards,

Kaushik Solanki

[#9]

Thanks Kaushik

I want to avoid having to create a zero (0) transaction per category. However, if I do work on the second option you gave me, I'm worried that there will be issues because option #1 is not in place... (i.e. there will still be issues with the transactions not existant for that month - i.e. no average).

Would it be possible to separate the exchange rate table altogether (because the LINK is not even required). All i need is the average for the YEAR and MONTHS selected.. which i could possibly use getSelectedValues(YEAR) for.. correct?

James

[kaushiknsolanki]

Hi,

     So do you mean you need avg exchange rate same for income and expense.

     Exchange rate will remain same for both?

Regards,

Kaushik Solanki