Aggr ordering

[RottieGirl]

Hello,

Can someone help me with the ordering when using AGGR()? (I'm am very new to qlik so I hopefully I'm using the correct terminololgy.)

I'm trying to calculate the cumulative total for the FY using the expression below:  (red line only, the other two are done using a different expression).

aggr(rangesum(Above(total Sum({$<[REPORT DATE.autoCalendar.FiscalYear]={'FY2020'}>}[MIPR SENT OBLI AMT]),0,RowNo())),[REPORT DATE.autoCalendar.FiscalMonth])
+aggr(rangesum(Above(total Sum({$<[REPORT DATE.autoCalendar.FiscalYear]={'FY2020'}>}[CC SENT AMOUNT]),0,RowNo())),[REPORT DATE.autoCalendar.FiscalMonth])

But, it will not sort according to the Fiscal Year, even when I use a sort expression.  The months are shown correctly, but the Dec and Feb totals are swapped.

My script editor shows: SET FirstMonthOfYear=10; --- if that helps.

I've tried every setting I can find to force the months to order correctly, but nothing has worked so far.

Alternative calculations have worked for both the cumulative total and the month order, but then I get a trailing line across the chart for the rest of the FY, which I do not want:

RangeSum(Above(TOTAL Sum({$<[REPORT DATE.autoCalendar.FiscalYear]={'FY2020'}>}[MIPR SENT OBLI AMT]),0,RowNo(TOTAL)))+RangeSum(Above(TOTAL Sum({$<[REPORT DATE.autoCalendar.FiscalYear]={'FY2020'}>}[CC SENT AMOUNT]),0,RowNo(TOTAL)))

(FYI - This is a large data set using a data connection to our financial system, so I have been hesitant to change anything in the script editor....not sure what can be changed w/o messing up the data connection. We are trying to automate this data pull for monthly reporting.)

[RottieGirl · Accepted solution]

I finally got this one.   Cumulative total w/ no trailing line and months in the correct order using a sort expression.  Posting here in case someone else has a similar issue.  May not be the prettiest solution, but it works so I'm going with it.

IF(SUM({$<[REPORT DATE.autoCalendar.FiscalMonth]={'Oct','Nov','Dec','Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep'},[REPORT DATE.autoCalendar.FiscalYear]={'FY2020'}>}[MIPR SENT OBLI AMT])+SUM({$<[REPORT DATE.autoCalendar.FiscalMonth]={'Oct','Nov','Dec','Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep'},[REPORT DATE.autoCalendar.FiscalYear]={'FY2020'}>}[CC SENT AMOUNT])= 0 ,'-',

RangeSum(Above(TOTAL Sum({$<[REPORT DATE.autoCalendar.FiscalMonth]={'Oct','Nov','Dec','Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep'},[REPORT DATE.autoCalendar.FiscalYear]={'FY2020'}>}[MIPR SENT OBLI AMT]),0,RowNo()))+RangeSum(Above(TOTAL Sum({$<[REPORT DATE.autoCalendar.FiscalMonth]={'Oct','Nov','Dec','Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep'},[REPORT DATE.autoCalendar.FiscalYear]={'FY2020'}>}[CC SENT AMOUNT]),0,RowNo())))