Or, in CS terminology, multilevel sorting is achieved by stable-sorting the innermost key first, then by the intermediate keys until the top-level key.
Unfortunately with the current qof-query mechanism, the top-level key compares dates rather than quarters. Therefore the split list has to be sorted strictly by date and will split up the inner key sorting (i.e. other-accname) inappropriately. If the qof-query mechanism had the ability to compare quarters instead, it would find that all these transactions belong to the same quarter therefore the list (nicely sorted by other-accname) shouldn't be disturbed. On 22 September 2017 at 23:41, Christopher Lam <christopher....@gmail.com> wrote: > Hi John, > > > > The origin of this project comes from my wish to produce report... "how > much did I spend on various categories, in this quarter?" > > > Source account=Asset:Bank. > > Prime-sortkey=date > > Prime-subtotal=quarterly > > Sec-sortkey=other-accname > > Sec-subtotal=true > > > > 2016Q1 > > Expense:Groceries > > 01/01/16 – Allmart $200 > > 01/02/16 – Qmart $300 > > 01/03/16 – Nomart $300 > > *Subtotal Expense:Groceries $800* > > Expense:Petrol > > 15/01/16 – Coral $300 > > 15/02/16 – BQ $250 > > * Subtotal Expense:Petrol $550* > > * Subtotal 2016Q1 = $1350* > > > > However the current query mechanism means that the dates will be split up, > because, although they belong to the same quarter, they have different > dates. > > qof-query mechanism cannot sort by quarter... can only sort by date. > > > I attach 2 screenshots to illustrate and the sample test file. This is my > amended transaction.scm and the original/custom sorter can be chosen in the > checkbox. > > > C > > On 22 September 2017 at 23:33, Christopher Lam <christopher....@gmail.com> > wrote: > >> Hi John, >> >> >> >> The origin of this project comes from my wish to produce report. >> >> Source account=Asset:Bank. Prime-sortkey=date (subtotal quarterly) and >> sec-sortkey=other-accname (subtotal=true). >> >> >> >> 2016Q1 >> >> Expense:Groceries >> >> 01/01/16 – Allmart $200 >> >> 01/02/16 – Qmart $300 >> >> 01/03/16 – Nomart $300 >> >> *Subtotal Expense:Groceries $800* >> >> Expense:Petrol >> >> 15/01/16 – Coral $300 >> >> 15/02/16 – BQ $250 >> >> * Subtotal Expense:Petrol $550* >> >> * Subtotal 2016Q1 = $1350* >> >> >> >> However the current query mechanism means that the dates will be split >> up, because they’ >> >> >> >> Sent from Mail <https://go.microsoft.com/fwlink/?LinkId=550986> for >> Windows 10 >> >> >> >> *From: *John Ralls <jra...@ceridwen.us> >> *Sent: *Friday, 22 September 2017 10:40 PM >> *To: *Christopher Lam <christopher....@gmail.com> >> *Cc: *gnucash-de...@lists.gnucash.org >> *Subject: *Re: transaction.scm date subtotal options - calling wizards >> ... >> >> >> >> >> >> >> >> On Sep 22, 2017, at 5:48 AM, Christopher Lam <christopher....@gmail.com> >> wrote: >> >> >> >> Hi devs, >> >> While working on transaction.scm (again) to fix a sorting/grouping bug[1], >> I came across some inconsistencies: >> >> 1. if I choose sortkey "date" "exact-time" -- they do the exact same >> thing, >> but otherwise work well >> >> 2. if I choose sortkey "reconciled date" -- it does what it says, however >> *the date-subtotal selector is not used at all.* >> >> 3. if I choose sortkey "register order" >> (a) first, I'm not entirely sure what exactly this mean - *does it mean, >> sort by EntryDate *(accessible by xaccTransGetDateEntered >> <https://wiki.gnucash.org/docs/MASTER/group__Transaction.htm >> l#gaa03b01c98f9ac644074a19a182cd5dd3>) >> >> (b) second, it allows date-subtotal selection, which to me is puzzling, >> but >> could be acceptable if we're sorting by EntryDate >> >> My conclusion from above would be that the original date-sorting-types has >> a typo, it was (define date-sorting-types (list 'date 'exact-time >> 'register-order)) but it should really be (define date-sorting-types (list >> 'date 'exact-time 'reconciled-date)) >> >> *What would be the consensus from wizards if we had to do it right?* >> >> I suggest: >> 1. We change (define date-sorting-types (list 'date 'exact-time >> 'reconciled-date)) >> 2. When sorting by register-order, it should disallow date-subtotals >> >> My solution to produce my report correctly requires a custom sorting >> algorithm which is nearly complete, and would override the >> qof-query-set-sort-order. Work-in-progress at >> https://github.com/christopherlam/gnucash/commits/master-fix >> -grouping-by-date >> >> [1] this bug relates to my preferred sorting/grouping: prime-sortkey=date, >> subtotal=quarterly, sec-sortkey=account-name, subtotal=true. This will >> produce an incorrect grouping whereby a quarter (eg 2016 Q1) will be split >> incorrectly, because the query will sort by dates rather than quarters. >> Documented in my comment on >> https://bugzilla.gnome.org/show_bug.cgi?id=626385 >> >> >> >> Christopher, >> >> >> >> I would expect “Register Order” to mean the normal sorting order of >> transactions in the register: >> >> date-posted, num, date-entered, description, though it’s possible that it >> would look at the Sort By… settings on the source register. >> >> >> >> “Date” should be “date-posted” for clarity. While a sort of date-entered >> might be amusing it’s not particularly useful. Exact time on date-posted >> isn’t meaningful: All transactions are posted at 10:59Z so that the date >> remains the same in most time zones. Similarly I’d expect that most >> date-reconciled entries will be grouped on a single date and time since >> it’s unusual to reconcile an account more than once per day (or even once >> per month for most people). >> >> >> >> I don’t understand your analysis that "a quarter (eg 2016 Q1) will be >> split incorrectly, because the query will sort by dates rather than >> quarters”. Sorting by quarters is just a large-granularity sort by date; a >> sort by date at day granularity should be easily divided by quarters. >> >> >> >> Regards, >> >> John Ralls >> >> >> >> >> >> >> > > _______________________________________________ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
