topic Average Income Calculation for max-month in QlikView

Average Income Calculation for max-month

Tue, 04 Aug 2015 08:42:04 GMT

Hi,

When trying to calculate Average Income for the YTD period, I use this and it works, but

sum({$<Year={ $(=max(Year)) }>} Commission_Amount)+Sum ({$<Year={ $(=max(Year)) }>} PIP_Profit_or_Loss)) / sum({$<Year={ $(=max(Year)) }>} Volume)

but when I try to do the same calculation for the max month , it does not work. This is how I am doing it

=sum({<MonthNumber= {$(=max(MonthNumber))},Month= ,[Month-Year]=>}Commission_Amount)+ sum({<MonthNumber= {$(=max(MonthNumber))},Month= ,

[Month-Year]=>}PIP_Profit_or_Loss )/sum({<MonthNumber= {$(=max(MonthNumber))},Month= ,[Month-Year]=>}Volume)

Please help me.

Yolanda

Re: Average Income Calculation for max-month

Gysbert_Wassenaar — Tue, 04 Aug 2015 08:58:24 GMT

Please post a qlikview document that demonstrates the problem.

Re: Average Income Calculation for max-month

Tue, 04 Aug 2015 09:13:19 GMT

Please find attached

Re: Average Income Calculation for max-month

Gysbert_Wassenaar — Tue, 04 Aug 2015 09:27:26 GMT

Can you tell me what's not working? I'm afraid I don't understand what result you want to see where.

Re: Average Income Calculation for max-month

Tue, 04 Aug 2015 09:39:31 GMT

I want the month calculation to work like the YTD Calc so I can calculate income for the YTD period and for the current month period.

Re: Average Income Calculation for max-month

Gysbert_Wassenaar — Tue, 04 Aug 2015 09:44:59 GMT

As far as I can see that's exactly what the expression do. I don't see any wrong results.

Re: Average Income Calculation for max-month

Tue, 04 Aug 2015 09:47:32 GMT

Yeah, Its works when I split it up (Income and Volume) but just does not give the right answer when I put it together (Income/Volume).

Re: Average Income Calculation for max-month

Gysbert_Wassenaar — Tue, 04 Aug 2015 09:52:09 GMT

Make sure to put the parentheses in the right places. a+b/c is not the same as (a+b)/c.

Re: Average Income Calculation for max-month

Tue, 04 Aug 2015 10:24:44 GMT

That is also not working, gives me 0.

Do you think anything else needs to be done?

Re: Average Income Calculation for max-month

Gysbert_Wassenaar — Tue, 04 Aug 2015 10:44:15 GMT

I have no idea because I don't see any problem. It works fine for me. See attached qvw.