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
