topic Re: If Formula in QlikView

If Formula

Thu, 27 Nov 2014 11:30:53 GMT

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

Re: If Formula

Anonymous — Thu, 27 Nov 2014 11:34:44 GMT

use this

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

Re: If Formula

Thu, 27 Nov 2014 11:39:12 GMT

Hi, Thanks for you help

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

Any more thoughts??

Re: If Formula

PrashantSangle — Thu, 27 Nov 2014 11:43:20 GMT

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

Re: If Formula

Anonymous — Thu, 27 Nov 2014 11:48:45 GMT

Use this

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

Re: Re: If Formula

Anonymous — Thu, 27 Nov 2014 11:53:47 GMT

Use like

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

Re: If Formula

marcus_malinow — Thu, 27 Nov 2014 12:01:46 GMT

Just use

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

and in the expression settings use Full Accumulation

Re: If Formula

jonathandienst — Thu, 27 Nov 2014 12:08:03 GMT

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

Re: If Formula

Thu, 27 Nov 2014 12:08:43 GMT

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.

Re: If Formula

giacomom — Thu, 27 Nov 2014 12:18:53 GMT

Hi Andrew,

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

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

Regards,

Giacomo