transaction.scm date subtotal options - calling wizards ...

[Christopher Lam]

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.lck at 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.lck at 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 <jralls at ceridwen.us>
>> *Sent: *Friday, 22 September 2017 10:40 PM
>> *To: *Christopher Lam <christopher.lck at gmail.com>
>> *Cc: *gnucash-devel at lists.gnucash.org
>> *Subject: *Re: transaction.scm date subtotal options - calling wizards
>> ...
>>
>>
>>
>>
>>
>>
>>
>> On Sep 22, 2017, at 5:48 AM, Christopher Lam <christopher.lck at 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
>>
>>
>>
>>
>>
>>
>>
>
>