topic Sorting accumulated straight table output in QlikView

Sorting accumulated straight table output

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

Hello,

I'm having a difficult time trying to sort by output of my straight table. I have dates from a database and Overnight output. I've use the date as the dimension and used the following rangesum formula to sum the revenue related to a range of 10 dates total (anything between the date and 9 dates prior).

=RangeSum(Above(Sum(Overnight),0, 10))

Unfortunately I can only sort my table by date because the output is dependent on the order of the dates. I agree with the output since I do want to make sure the range that is used is based on a calendar order of dates. However, I want to see the top 10 negative outputs by dates. So which consecutive 10 dates have the largest loss in 'Overnight'.

Any ideas how to do this?

Re: Sorting accumulated straight table output

sunny_talwar — Wed, 26 Jul 2017 19:18:28 GMT

If your date field is sorted in ascending order in the script (which would most likely be true if you have a master calendar), you might be able to use this

Aggr(RangeSum(Above(Sum(Overnight),0, 10)), DateField)

and in case you have QV12 or above, you can make use of The sortable Aggr function is finally here!

Aggr(RangeSum(Above(Sum(Overnight),0, 10)), (DateField, (NUMERIC)))

Re: Sorting accumulated straight table output

sarahshong — Wed, 26 Jul 2017 19:49:42 GMT

Thanks Sunny! that worked once I included an Order by function in my load script.