Get Free Trial
Get Free Trial

Distinct count is incorrect when using a field of type year/quarter/month/day or year/month/day

posted on April 2nd, 2019

Resolved
Projet TCD asked on April 2, 2019
  • Version: Pivot Table for JSON data sources v2.7.2
  • Browser: Chrome Version 73.0.3683.86 on MacOS 10.14.1 (18B75)
  • Link to reproduce: http://jsfiddle.net/0d3kztux/3/
  • Steps to reproduce:
    • Display pivot table in attached JSFiddle

 

  • Observed behavior:
    • Cells values in row "Grand Total" for the columns:
      • Total Distinct Count of Date de valeur (1)
      • Total Distinct Count of Date de modification (1)
      • Total Distinct Count of Raison Sociale (0)
    • Cells values in rows "FR" and "US" for column:
      • Total Distinct Count of Raison Sociale (0 and 0)
    • No JS console errors

 

  • Expected behavior:
    • Cells values in row "Grand Total" for the columns:
      • Total Distinct Count of Date de valeur (2)
      • Total Distinct Count of Date de modification (2)
      • Total Distinct Count of Raison Sociale (2)
    • Cells values in rows "FR" and "US" for column:
      • Total Distinct Count of Raison Sociale (1 and 1)

 

  • More details on observed behavior:
    • Could be 2 issues combined
      • Handling of dates in distinct count being incorrect (1 instead of 2)
      • Handling of empty data 
    • Values in columns can change (and still be wrong overall) depending on:
      • Whether the data type for the fields "date_valeur" or "date_mod" is "year/quarter/month/day" or "year/month/day" or other date types
      • Whether the last data item ("siret") is empty ("") or not
    • The only configuration where every count (from the included fields) is correct is when:
      • Dates are all in "date string" type
      • Data for the last field (here "siret") is never empty
    • There seems to be a pattern on the "0" column depending on the date fields format:
      • When both dates are "year/quarter/month/day" and "year/quarter/month/day" (8 date sub-fields) (9 fields total)
        • Column 4 is all "0"
      • When one date is "year/quarter/month/day" and the other "year/month/day" (7 date sub-fields) (9 fields total)
        • Column 5 is all "0"
      • When both dates are "year/month/day" and "year/month/day" (6 date sub-fields) (9 fields total)
        • Column 6 is all "0"
      • When one date is "year/quarter/month/day" and the other "date string" (5 date sub-fields) (9 fields total)
        • Column 7 is all "0"
      • When one date is "year/month/day" and the other "date string" (4 date sub-fields) (9 fields total)
        • Column 8 is all "0"
      • When both dates are "date string" and "date string" (2 date fields) (9 fields total)
        • Column 10 is all "0"
    • But:
      • When one date is "year/month/day" and the other REMOVED (3 date sub-fields) (8 fields total)
        • Column 7 is all "0"
      • When one date is "date string" and the other REMOVED (1 date field) (8 fields total)
        • Column 9 is all "0"
    • Still no idea on the logic behind that bug, but here is an interesting case which points to a combination: http://jsfiddle.net/0d3kztux/5/
      • When one data entry has the last field ("siret") not empty, the column 3 will contain a "1" on the associated row (while the other will be 0) and total will be 1 (incorrect)
      • When both data entry have the last field ("siret") not empty, the column 3 will contain a "1" on both rows, and total will be "2" (correct)

Attachments:
Capture d’écran 2019-04-02 à 19.03.25.png
Capture d’écran 2019-04-02 à 19.03.35.png

2 answers

Public
Vera Didenko Vera Didenko ⋅ Flexmonster ⋅ April 3, 2019

Hello,

Thank you for reporting this issue to us in such detail and providing necessary screenshots and JSFiddles for illustration.

The problem is reproducible on our side and we are currently working on a solution.
The fix will be available in the minor release with the ETA May 6th.

Please let us know if it works for you.

Best Regards,
Vera

Public
Vera Didenko Vera Didenko ⋅ Flexmonster ⋅ May 8, 2019

Hello,
 
We are glad to inform you that the issue with distinct count aggregation for hierarchies with multiple levels (e.g., year/month/day) was fixed.
The fix is available on our website in the minor release 2.7.5 of Flexmonster.
You are welcome to update the component.
 
Please let us know if everything works fine for you.
 
Best Regards,
Vera

Please login or Register to Submit Answer