[Libreoffice-bugs] [Bug 113002] LibreOfficeCalc: Pivot Table recurring data field name will added a simple quotation marks before number

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=113002

--- Comment #16 from Robert Lacroix <ralacroix at hydro.mb.ca> ---
One more comment about the repeated item label values for dates. The text
strings produced for repeated item labels follow the formatting of the source
column. So dates in the source formatted as "MMM YYYY" produce 8-character
values for text item labels, while dates in the source formatted as
"YYYY-MM-DD" produce 10-character values for text item labels. Meanwhile the
value of the numeric item labels in the PT retain full date precision
regardless of the formatting of the source column or formatting of the pivot
table cells.

It bears pointing out that under all circumstances, the VALUE of an item label
should reflect the value of the source column (whether it's an original item
label or a repeated item label). The FORMAT of the item label should reflect,
primarily, the format applied to cells in the pivot table, and if otherwise
cleared of formatting, secondarily to the format of the source column. IT IS A
SERIOUS BUG to use the formatted appearance of a source column for the value of
an item label.

TLDR; stop here.

To belabour the point, let's look at an example that would compute COST OF
GOODS SOLD in a business' quarterly income statement, where the fiscal year end
is July 31. (NB: real accounting software should be used instead of LO-Calc,
but we are talking about a hypothetical example; CAN and SHOULD are
irrelevant).

The source data is a list of sales transactions. Among the data columns are:
"tr_date" the transaction date of an invoice
"barcode" of each item sold on an invoice
"quantity" of each item sold on an invoice

A new source column "qe_date" is added which computes the last date of a fiscal
quarter (it's pretty ugly).

=EOMONTH(tr_date,(INT(MOD((MONTH(tr_date)+4),12)/3)+1)*3+7-MONTH(tr_date))

A pivot table is used to tabulate the total number of each item sold by barcode
in every fiscal quarter. The PT fields are
"qe_date_field" the result of selecting "qe_date" as a row field
"barcode_field" the result of selecting "barcode" as a row field
"sum(quantity)" the data field

In the leftmost row postion, the "Repeat item labels" option must be turned on
for "qe_date_field" since we want to use that field to look up the quarterly
average price of inventory in another table for computing COST OF GOODS SOLD
for the quarter. The price lookup could be done in the source table, but let's
follow through this example because there might be only 160 lookups in the PT
compared to doing this in the source table which might be 2000 lookups.

For item labels of "qe_date_field" which are numeric, a simple cell reference
suffices to extract the relevant quarter-ending date.

=qe_date_field

But for text items labels it's a different story. We must use DATEVALUE() to
get the date. But DATEVALUE produces an error for numeric fields, we must test
"qe_date_field" thusly:

=IF(ISNUMBER(qe_date_field),qe_date_field,DATEVALUE(qe_date_field))

This works fine when "YYYY-MM-DD" is the date format of the "qe_date" source
column. All PT item labels, including repeated labels, show the correct
quarter-ending date.

But say the source column uses a natural date format for quarter-ending. Using
the date format "MMM YYYY", DATEVALUE(qe_date_field) produces the first day of
the month instead of the last, so the cell reference must be

=IF(ISNUMBER(qe_date_field),qe_date_field,EOMONTH(DATEVALUE(qe_date_field)))

--or--

=IF(ISNUMBER(qe_date_field),qe_date_field,DATEVALUE(qe_date_field)+30))


Long story short, the cell reference needed to work around this bug is both
complex and fragile (being subject to the whims of source column formatting).

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20201227/b2c6efc8/attachment-0001.htm>