Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
carolin01
Luminary Alumni
Luminary Alumni

Aggr(Rangesum(above(sum

Hi,

I´ve a nice aggr(rangesum(above example attached. What I don´t get yet is the following: In my example file you find a column in the left straight table that includes the column "Rangesum Passed per Year". The result is already what I want. But I do not want to see only the totals. And the totals also does not make sense in the first month of the year. I want the aggregation for each of the months and then stop when the year switches and start over with the next year. Is this possible?

Many thanks in advance!

6 Replies
carolin01
Luminary Alumni
Luminary Alumni
Author

I detected another issue. It looks like as soon as the column "Rangesum Passed per Year" is activated it changes my values in the other columns like "Rangesum Passed", "Rangesum Total" and "Rangesum % Calculated". This is strange. Why is it affecting it?

Gysbert_Wassenaar

No idea. After disabling and re-enabling the fourth expression (also called Rangesum Passed just like the first expression) it all looks fine to me. The running total resets on year borders.

You could add a nodistinct in the aggr of the fourth expression so you get the year total at every row.


talk is cheap, supply exceeds demand
carolin01
Luminary Alumni
Luminary Alumni
Author

If I enable the "Rangesum Passed per Year" it changes the column "Rangesum Passed" and "Rangesum Total". If it is enabled the values get incorrect if it is disabled the other two expressions are correct again.

Is it

=Num(nodistinct(Aggr(Rangesum(Above(sum({$<Result = {'Passed'}>}Quantity),0,1)),Year)),$(vInteger))

then ? I do not want the same total in every row. I want it to be aggregated step by step and then stop at the end ot the last preriod of the year. Then it should start over with the next year.

juraj_misina
Luminary Alumni
Luminary Alumni

Strange, this should work OK.

You can work with TOTAL qualifier and make the Above function go as many steps up as needed.

Num(Rangesum(Above(TOTAL sum({$<Result = {'Passed'}>}Quantity),0,If(Mod(RowNo(TOTAL), 12)=0, 12, Mod(RowNo(TOTAL), 12)))),$(vInteger))

This will work only for full years, but I guess it could be adjusted with GetPossibleCount(YearMonth).

juraj_misina
Luminary Alumni
Luminary Alumni

Plus, I noticed that the result of the first expression is affected by sorting. If you sort by Year, it works fine. If you sort by YearMonth,  it does not work. I guess this affects how Above() handles dimensions and whether or not the function is in fact "able" to see on the row above it without TOTAL qualifier.

My way of debugging expressions like this is adding a dummy expression containing only RowNo() to see how dimensions and sort order affects the results.AboveVsSort.PNG

Gysbert_Wassenaar

Ok, it's the interactive sorting that causing the problem. For the rangesum(above( ... )) construct to work correctly the data must be sorted by Year first and YearMonth secondly. Otherwise every YearMonth will be a border where the running total gets reset. So you need to fix the sort order on the Sort tab and disable the Allow Interactive Sort option.


talk is cheap, supply exceeds demand