topic Re: Average ignore columns in QlikView

Average ignore columns

Mon, 29 Jul 2013 11:52:51 GMT

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

Re: Average ignore columns

kaushiknsolanki — Mon, 29 Jul 2013 12:03:36 GMT

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

Re: Average ignore columns

daveamz — Mon, 29 Jul 2013 12:18:25 GMT

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

Re: Average ignore columns

Mon, 29 Jul 2013 12:27:05 GMT

Thanks Kaushik

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

thanks a mil

James

Re: Average ignore columns

Mon, 29 Jul 2013 12:37:06 GMT

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)

Re: Average ignore columns

kaushiknsolanki — Mon, 29 Jul 2013 12:49:41 GMT

Hi,

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

Regards,

Kaushik Solanki

Re: Average ignore columns

Mon, 29 Jul 2013 12:51:25 GMT

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

Re: Average ignore columns

kaushiknsolanki — Mon, 29 Jul 2013 13:00:18 GMT

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

Re: Average ignore columns

Mon, 29 Jul 2013 13:20:02 GMT

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

Re: Average ignore columns

kaushiknsolanki — Mon, 29 Jul 2013 13:56:22 GMT

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

Re: Average ignore columns

Mon, 29 Jul 2013 14:02:00 GMT

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?

Re: Average ignore columns

daveamz — Mon, 29 Jul 2013 14:24:11 GMT

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

Re: Average ignore columns

Mon, 29 Jul 2013 16:39:22 GMT

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?

Re: Average ignore columns

daveamz — Tue, 30 Jul 2013 06:29:04 GMT

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

Re: Average ignore columns

Tue, 30 Jul 2013 08:13:11 GMT

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

Re: Average ignore columns

daveamz — Tue, 30 Jul 2013 08:48:10 GMT

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

Re: Average ignore columns

brijesh1991 — Tue, 30 Jul 2013 09:23:04 GMT

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

Re: Average ignore columns

Tue, 30 Jul 2013 09:31:49 GMT

Thanks a mil guys!

Re: Average ignore columns

Tue, 20 Aug 2013 07:06:19 GMT

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!