Above function with two dimensions not working

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi Qlik,

I'm facing issue with aggr and above function . Actually i want to show the closing balance as the opening balance to the next month.

By using single dimension Year-Month it showing values exactly what i expected.

Using Single dimension Result:

Year-Month	Open Balance	Created	Closed	Closing Balance
2018-Jan	0	10	5	5
2018-Feb	5	7	5	7
2018-Mar	7	12	10	9

Formula used for Open Balance is

=num(SUM(aggr(above(RangeSum(Above(total count({<Date_Type={'CreatedDateRelated'}>}distinct ticket_id) , 0, RowNo()))

RangeSum(Above(total Count({<Date_Type={'ClosedDateRelated'}>}distinct ticket_id) , 0, RowNo()))),Year_Month1)))

When adding dimensions Year-Month and Priority its not showing exactly what i expected

Two dimensions Showing Result As:

-Year-Month -Priority	Open Balance	Created	Closed	Closing Balance
2018-Jan
L1	0	5	2	3
L2	0	5	3	2
2018-Jan Total	0	10	5	5
2018-Feb
L2	2	7	5	4
2018-Feb Total	2	7	5	4
2018-Mar
L1	3	6	5	4
L2	4	6	5	5
2018-Mar Total	7	12	10	9
Grand Total	9	29	20	18

Here '2018 - Feb' total Open balance is showing as 2 but total open balance should show 5 ,Because previous closing balance is 5 which is carry forward to '2018-Feb'.Here L1 priority activities not there in the Feb month thats why its not showing L1 Open balance count 3.

Excepting Result like this:

-Year-Month -Priority	Open Balance	Created	Closed	Closing Balance
2018-Jan
L1	0	5	2	3
L2	0	5	3	2
2018-Jan Total	0	10	5	5
2018-Feb
L1	3	0	0	0
L2	2	7	5	4
2018-Feb Total	5	7	5	4
2018-Mar
L1	3	6	5	4
L2	4	6	5	5
2018-Mar Total	7	12	10	9
Grand Total	9	29	20	18

Please help me in this.

Thanks and Regards

M Subash Chandra

Re: Above function with two dimensions not working

ogautier62 — Mon, 02 Jul 2018 09:04:00 GMT

Hi,

may be use just above(closing balance) for open balance without rangesum

regards

Re: Above function with two dimensions not working

Anonymous — Mon, 02 Jul 2018 09:13:39 GMT

Hi Olivier,

I Tried by using above function without using rangesum but it didn't work as i expected.

Regards

M Subash

Re: Above function with two dimensions not working

ogautier62 — Mon, 02 Jul 2018 09:27:54 GMT

what's wrong axactly ?

it should work fine

topic Re: Above function with two dimensions not working in QlikView

Above function with two dimensions not working

Re: Above function with two dimensions not working

Re: Above function with two dimensions not working

Re: Above function with two dimensions not working