topic Whats wrong with this simple AGGR()? in QlikView

Whats wrong with this simple AGGR()?

Mon, 15 Aug 2011 13:13:27 GMT

Can some one please help me and explain whats wrong with the AGGR statement below. I tried working around this statement changing the functions, but its no use. I have total values at month level and unable to get the running totals using the AGGR(). I just have 4 months data. Its really annoying me for sometime now.

sum(aggr(sum(vLastFctPayment.Amount)-(sum(Expenses.Expenses)-sum(vLastFctPayment.ServicingFee)),Calendar.YearMonth))

I have above expression within a variable called $(vCashToMonth) and I even tried aggr($(vCashToMonth), Calendar.YearMonth) still doesnt work.

Thanks a ton in Advance,

Mady

Whats wrong with this simple AGGR()?

swuehl — Mon, 15 Aug 2011 13:25:52 GMT

Hi,

are you using variables within your aggr? (Just thinking because of the 'v' at the beginning of field Names)

If so, what do they look like?

If not, the expression look good to me, could you post a small sample app here?

Regards,

Stefan

Whats wrong with this simple AGGR()?

erichshiino — Mon, 15 Aug 2011 13:41:07 GMT

Does it work if you select only one Calendar.YearMonth?

Does it work without Aggr ? (sum(vLastFctPayment.Amount)-(sum(Expenses.Expenses)-sum(vLastFctPayment.ServicingFee))

Whats wrong with this simple AGGR()?

Mon, 15 Aug 2011 13:43:19 GMT

Hello Erich,

yes it works for one Calendar.YearMonth and even the expression works normally if I dont add AGGR. But when I use it in a pivot or else where I have problem without the AGGR().

I have a sample, just not sure how to attach a QVW here. Its a real pain.

Can you please help?

Thanks

Mady

Whats wrong with this simple AGGR()?

Mon, 15 Aug 2011 13:50:25 GMT

Sample attache now, please check the first post.

Thanks,

Mady

Re: Whats wrong with this simple AGGR()?

lironbaram — Mon, 15 Aug 2011 13:50:54 GMT

hei

try this one

hope its helps you

Whats wrong with this simple AGGR()?

Mon, 15 Aug 2011 14:23:48 GMT

Hello Liron,

I am sorry but I was trying to get the running totals, and in your attachment it looks like, its not working. Please correct me if I am missing something here.

Thanks again for your effort.

Re: Whats wrong with this simple AGGR()?

lironbaram — Mon, 15 Aug 2011 14:32:14 GMT

you are correct

try this one

Whats wrong with this simple AGGR()?

swuehl — Mon, 15 Aug 2011 14:35:54 GMT

Lirons solution looks ok, to me.

An alternative using rangesum:

=rangesum(before(sum(Amount),0, columnNo()))

Please take care, if you change the pivot orientation (Year-Month vertical instead of horizontal) you must replace before() with above() and columnNo() with rowNo().

Whats wrong with this simple AGGR()?

Mon, 15 Aug 2011 15:25:36 GMT

Hello Liron,

Thankyou for the help and I think I am getting what I wanted, But what if the expression for month calculation is

sum(Amount) - (sum(Expenses)-sum(vLastFctPayment.ServicingFee) ) how do I add this to the formula which is suggested by you here as seen:

=sum(Amount)+if(isnull(after(sum(Amount))),0,after([Amount1]))

Thanks Again,

Mady

Whats wrong with this simple AGGR()?

Mon, 15 Aug 2011 15:48:34 GMT

Hello Liron,

Please ignore my previous post, as i figured it out and thanks for all your help. But my original question still remains unaswered as to why the AGGR dint work here?

Can you explain please?

Thanks,

Mady