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

[#2]

Well, I dont need the exchange rate table to be linked to the transactions table. All i need is the average exchange rate based on the MONTH and YEAR selected.

so i changed the date fields in the exchange rate table to EXMONTH and EXYEAR. And this works fine so far:

AVG(if(GetFieldSelections(YEAR)=EXYEAR and GetFieldSelections(MONTH)=EXMONTH,EXRATE))

but - if the user selected more than a single month, the GetFieldSelections(MONTH) is e.g. "Jan, Feb, Mar". (or 'ALL' if the user selects all the months...

is there a way to cater for these possible combinations?

[daveamz]

Kaushik is right, you need to define what EXRATE represents. If the EXRATE is the avg of month by year then you can link the second table aggregated.

LOAD YEAR&'_'&MONTH as LINK

          AVG(EXRATE) as EXRATE

FROM ...

GROUP BY YEAR, MONTH;

This one will linkk for each record from table 1 the average EXRATE from that month and year from table 2

Regards,

David

[#4]

hi David

i feel i will be stuck with the same issue though. Because there are no transactions for category EXPENSE in March, the avg(exrate) value will only show values until february.. (which is incorrect).

Option #1 above is the correct option, as it will include values for months where no transactions exist. But this is not an easy task as each category can have up to 100 accounts. Which means i need to create a zero 0.00 line for each account in the system for each month up and until toDate.

What i have been testing is separating the two tables, and using the getSelectedValue(YEAR) =EXYEAR. And this works, except there can be multiple selections for MONTH so im having difficulty making EXMONTH = 'Jan, Feb, Mar'... (if you understanding what i mean)..

So now i'm trying to find a way around that. Any suggestions?

[daveamz]

Ok, I used your approach and I attached my result.

Use a variable like vExRate = getfieldselections(MONTH)

Without any link between first table and second table use set analysis:

Avg({<Rate.MONTH={$(vExRate)}>}Rate.EXRATE)

If you have multiple years I recommend to use something like YEAR&'_'&MONTH instead of MONTH.

Good luck,

David

[#6]

This works great David - thanks a million!

My only gripe at the moment is that when i selection all months, getfieldselctions(MONTH) equals "ALL"..

Do you know if there is a way around this?

thanks

[daveamz · Accepted solution]

Try something like:

=GetFieldSelections(MONTH, ',',12)

where 12 is the max possible values. If you have something like YEAR&MONTH I guess you can use another valule like 1000.

David

[brijesh1991]

hi, you can use this: Avg({}EXRATE)

[#9]

Thanks a mil guys!

[#10]

Hi daveamz01

I've run into another complication with this and hope you can help me with this one last time...

The above solution works with a single currency. So what i have done is isolated the transactions by currency:

(simplified):

sum(if(curr='zar',amt)) / avg(if(curr='zar',exRate))

+

sum(if(curr='EUR',amt)) / avg(if(curr='EUR',exRate))

+

...

+

...

I can get the result I want using the above, however it is not practical, as I have to add to the code whenever a new company (with a new currency) is added to the system.

Is there a way around this?

I have added a sample attached...

thanks a mil!