If Formula

[#1]

Hi All

I am having an issue with an IF formula and need some help.

I have data in excel which looks like this:

ID	FinYr	FinMth	FinAmt
1000008-G	2013	2	2059
1000018-G	2013	1	200
1000018-G	2013	3	-499
1000018-G	2013	7	-2503
1000020-G	2013	2	-2389
1000022-G	2013	1	-1070.31
1000024-G	2013	3	-1741
1000026-G	2013	1	20000
1000026-G	2013	3	-2001
1000026-G	2013	8	600
1000026-G	2013	10	461.25
1000026-G	2013	12	11400
1000026-G	2014	2	26600
1000026-G	2014	7	0
1000026-G	2014	10	8041.2
1000027-G	2013	3	-101
1000027-G	2013	4	288

I want my formula to say something like this.

if(Finyr = "2013", Sum(aggr(sum(FinAmt), 0 ))))

I will then plot it on a graph by the Fin month so i am getting the Aggr amount month on month.

My graph will look like this:

Can anyone help me with this formula??

Thanks

Andrew

[Anonymous]

Hi

use this

=Sum(Aggr(If(FinYr = '2013',FinAmt)))

[#3]

Hi, Thanks for you help

However it does not seem to work. It is still saying "Error in expression"

Any more thoughts??

[PrashantSangle]

Hi,

When you use AGGR() it will take min two parameters,

Just check your logic in your logic You are aggregating with 0 why?

Why you need aggr()?

Try like

if(Finyr = "2013",sum(FinAmt), 0 )

Regards

[Anonymous]

Hi

Use this

Sum(Aggr(If(FinYr='2013',FinAmt),FinAmt))

[Anonymous]

Hi

Use like

=Sum(aggr(if(FinYear = '2013',FinAmt)

[marcus_malinow · Accepted solution]

Just use

Sum({$<FinYear={'2013'}>} FinAmt)

and in the expression settings use Full Accumulation

[jonathandienst]

Hi

Do you mean a cumulative amount (that's what the graph looks like). You can do that by using  Sum(FinAmt) for the expression and checking one of the accumulate buttons on the expression dialog. Or you can use a RangeSum(Above()) construction to manually accumulate.

Do you want to filter to a particular year? Add a Year listbox so the user can select year and use Sum(FinAmt) as your expression. No need for an aggr() expression which will do nothing except possible harm performance if your data set is large. You can apply a conditional display or conditional calculation if you want to only display the chart if a year is selected.

HTH

Jonathan

[#9]

That's the one. I knew something was going wrong, I had the above formulas and also the expression setting set to full accumulation.

Thanks all for your help.

[giacomom]

Hi Andrew,

if you want to aggregate by months you can use the formula:

=Aggr(sum(If(FinYr='2013',FinAmt, 0)), FinMth)

Regards,

Giacomo