Aggr ordering

RottieGirl — Sat, 16 Nov 2024 03:02:38 GMT

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.)

Re: Aggr ordering

RottieGirl — Wed, 11 Mar 2020 14:31:48 GMT

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())))

topic Re: Aggr ordering in App Development

Aggr ordering

Re: Aggr ordering