topic Re: Why it does not aggr(rangesum) after i filter in QlikView

Why it does not aggr(rangesum) after i filter

raceberos — Tue, 19 Dec 2017 05:08:38 GMT

Edit1: I already upload the qvf file... for reference thanks..

Edit2: I update my formula

from: "((Aggr(RangeSum(Above(Sum({<GL2= {11}>} GLTOTAL), 0, RowNo())), BRANCHNAME, [DOCDATE.autoCalendar.Month])) ) - ((Aggr(RangeSum(Above(Sum({<GL2= {21}>} GLTOTAL), 0, RowNo())), BRANCHNAME, [DOCDATE.autoCalendar.Month])) *-1 )"

into:

from: "((Aggr(RangeSum(Above(Sum({<GL2= {11},[DOCDATE.autoCalendar.Month]=>} GLTOTAL), 0, RowNo())), BRANCHNAME, [DOCDATE.autoCalendar.Month])) ) - ((Aggr(RangeSum(Above(Sum({<GL2= {21},[DOCDATE.autoCalendar.Month]=>} GLTOTAL), 0, RowNo())), BRANCHNAME, [DOCDATE.autoCalendar.Month])) *-1 )"

Hi good day every one i just created a financial KPI last week with the help of some people here in our community and it was good... but after i check every details of it i notice that there is something went wrong after i filter...

the 1st chart is perfect the figure shows exactly what i want..

but after i filter a month everything went wrong...

ohh by the way this is my formula

"((Aggr(RangeSum(Above(Sum({<GL2= {11}>} GLTOTAL), 0, RowNo())), BRANCHNAME, [DOCDATE.autoCalendar.Month])) ) - ((Aggr(RangeSum(Above(Sum({<GL2= {21}>} GLTOTAL), 0, RowNo())), BRANCHNAME, [DOCDATE.autoCalendar.Month])) *-1 )"

can some one have any idea why it happens?

thanks

Message was edited by: Reynaldson Aceberos QVF file is added

Re: Why it does not aggr(rangesum) after i filter

Anil_Babu_Samineni — Tue, 19 Dec 2017 05:20:22 GMT

May be you did one filter extra from selector? If All fine, May be round up with Sum(Aggr(..))

Re: Why it does not aggr(rangesum) after i filter

raceberos — Tue, 19 Dec 2017 05:28:01 GMT

thanks for that quick reply

i got no luck mate i try to wrap my formula with sum()

sum(((Aggr(RangeSum(Above(Sum({<GL2= {11}>} GLTOTAL), 0, RowNo())), BRANCHNAME, [DOCDATE.autoCalendar.Month])) ) - ((Aggr(RangeSum(Above(Sum({<GL2= {21}>} GLTOTAL), 0, RowNo())), BRANCHNAME, [DOCDATE.autoCalendar.Month])) *-1 )) but still same result

Re: Why it does not aggr(rangesum) after i filter

Anil_Babu_Samineni — Tue, 19 Dec 2017 05:34:40 GMT

Equation should cover all elements like

(Sum(Aggr(RangeSum(Above(Sum({<GL2= {11}>} GLTOTAL), 0, RowNo())), BRANCHNAME, [DOCDATE.autoCalendar.Month])) - Sum(Aggr(RangeSum(Above(Sum({<GL2= {21}>} GLTOTAL), 0, RowNo())), BRANCHNAME, [DOCDATE.autoCalendar.Month])) *-1)

Re: Why it does not aggr(rangesum) after i filter

luismadriz — Tue, 19 Dec 2017 05:59:51 GMT

Hi,

Maybe the problem is the Above function (along with RowNo) as it may lose its meaning after the selection.

You may want to convert this chart to a table for review and also share the unaggregated data for troubleshooting?

Cheers,

Luis

Re: Why it does not aggr(rangesum) after i filter

raceberos — Tue, 19 Dec 2017 06:10:06 GMT

thanks mate

well i use same concept on total asset and i dont have any problem on it...

Aggr(RangeSum(Above(Sum({<GLHeader= {1}>} GLTOTAL), 0, RowNo())), BRANCHNAME, [DOCDATE.autoCalendar.Month])

then i use the same concept on getting for the working capital but i dont get what i expected result

(Sum(Aggr(RangeSum(Above(Sum({<GL2= {11}>} GLTOTAL), 0, RowNo())), BRANCHNAME, [DOCDATE.autoCalendar.Month])) - Sum(Aggr(RangeSum(Above(Sum({<GL2= {21}>} GLTOTAL), 0, RowNo())), BRANCHNAME, [DOCDATE.autoCalendar.Month])) *-1)

wait ill upload my qvf file

im looking where to attached it...

thanks

Re: Why it does not aggr(rangesum) after i filter

Anil_Babu_Samineni — Tue, 19 Dec 2017 06:14:00 GMT

My assumption may match in your rek, Perhaps this?

(Sum(Aggr(RangeSum(Above(TOTAL Sum({<GL2= {11}>} GLTOTAL), 0, RowNo())), BRANCHNAME, [DOCDATE.autoCalendar.Month])) - Sum(Aggr(RangeSum(Above(TOTAL Sum({<GL2= {21}>} GLTOTAL), 0, RowNo())), BRANCHNAME, [DOCDATE.autoCalendar.Month])) *-1)

Re: Why it does not aggr(rangesum) after i filter

luismadriz — Tue, 19 Dec 2017 06:30:32 GMT

Hi, you may not be able to upload it on this post, as it seems you can attach documents only when the post is created. I hope they fix that.....

If you want, send it via email

Cheers

Luis

Re: Why it does not aggr(rangesum) after i filter

raceberos — Tue, 19 Dec 2017 06:36:42 GMT

it takes me time to find it..

but i manage.. i edit my thread and upload the qvf..

thank you very much mate..

Re: Why it does not aggr(rangesum) after i filter

luismadriz — Tue, 19 Dec 2017 06:41:16 GMT

Total Assets also fails after selection.... I mean changes its value

I'll take a look,

Luis

Re: Why it does not aggr(rangesum) after i filter

raceberos — Tue, 19 Dec 2017 07:37:30 GMT

yeah thanks for heads up, i realize i keep on testing on asset on January where i put my begging balance so technically it will always return accurate result. then i check the other months then i see that it relay fail.. on selection

im still looking for the right solution...

Re: Why it does not aggr(rangesum) after i filter

luismadriz — Tue, 19 Dec 2017 23:24:07 GMT

Hi,

Do you know the right values you should be getting for each month? Have you done your calculations in Excel or something?

Cheers,

Luis

Re: Why it does not aggr(rangesum) after i filter

raceberos — Wed, 20 Dec 2017 03:21:03 GMT

i update my formula into

'=Aggr(RangeSum(Above(Sum({<GLHeader= {1}, [DOCDATE.autoCalendar.Month]=>} GLTOTAL), 0, RowNo())), Branch, Month)' and now even if i select any month it will not take any effects on figures...

thanks alot mate for helping me...

-rey