asked 3 days ago
Issue Description
I want to use flexmonster differenceofrow or differenceofcol measure to calculate differences between 2 adjacent aggregated measures. However, some rows are not getting the calculated results. Please refer to the flexmonster pivot reference zip file and the mock data set to replicate the scenario from your end.
Version Details
flexmonster pivot reference.zip file
Mock data set
INSERT INTO activity (id, operation, filename, tablename, command, who) VALUES (117, 'test console', 'test 1', 'test', 'test', 'test');
INSERT INTO activity (id, operation, filename, tablename, command, who) VALUES (118, 'test console', 'test 1', 'test', 'test', 'test');
INSERT INTO activity (id, operation, filename, tablename, command, who) VALUES (119, 'test console', 'test 2', 'test', 'test', 'test');
INSERT INTO activity (id, operation, filename, tablename, command, who) VALUES (120, 'test console', 'test 2', 'test', 'test', 'test');
INSERT INTO activity (id, operation, filename, tablename, command, who) VALUES (121, 'file ops', 'test 1', 'test', 'test', 'test');
INSERT INTO activity (id, operation, filename, tablename, command, who) VALUES (122, 'file ops', 'test', 'test', 'test', 'test');
INSERT INTO activity (id, operation, filename, tablename, command, who) VALUES (123, 'file ops', 'test', 'test', 'test', 'test');
Chart settings json used
{"slice":{"rows":[{"uniqueName":"filename"}],"columns":[{"uniqueName":"operation"},{"uniqueName":"[Measures]"}],"measures":[{"uniqueName":"id","aggregation":"sum","format":"-1i1i64la47eq0"},{"uniqueName":"id variance","formula":"differenceofrow(\"id\")","caption":"differenceofrow id"}]},"options":{"grid":{"showTotals":"off","showGrandTotals":"off","autoSwitchToCompact":false},"chart":{"activeMeasure":{"uniqueName":{}}}},"formats":[{"name":"-1i1i64la47eq0","nullValue":""}],"version":"2.9.133","creationDate":"2026-10-07T02:00:13.441Z","dataSource":{"mapping":{"filename":{"caption":"filename"}}}}
Chart options json used
{"viewType":"grid","grid":{"type":"compact","title":"","showFilter":true,"showHeaders":true,"showTotals":"off","showGrandTotals":"off","columnTotalsPosition":"before","grandTotalsPosition":"top","showExtraTotalLabels":false,"showHierarchies":true,"showHierarchyCaptions":true,"drillThroughMaxRows":1000,"showReportFiltersArea":true,"dragging":true,"showAutoCalculationBar":true,"autoSwitchToCompact":false,"disableExpandAndCollapse":false,"repeatAllLabels":false},"chart":{"type":"column","title":"","showFilter":true,"multipleMeasures":false,"oneLevel":false,"autoRange":false,"reversedAxes":false,"showLegend":true,"showLegendButton":false,"showDataLabels":false,"showAllLabels":false,"showMeasures":true,"showOneMeasureSelection":true,"showWarning":true,"activeMeasure":{"uniqueName":{}}},"filter":{"weekOffset":1,"dateFormat":"dd/MM/yyyy","liveSearch":true,"filterByDateAndTime":false,"allowEmptyMembersFilter":false},"configuratorActive":false,"configuratorButton":true,"showAggregations":true,"showCalculatedValuesButton":true,"grouping":false,"editing":false,"drillThrough":true,"showDrillThroughConfigurator":true,"sorting":"on","defaultDateType":"date","strictDataTypes":false,"datePattern":"dd/MM/yyyy","dateTimePattern":"dd/MM/yyyy HH:mm:ss","saveAllFormats":false,"showDefaultSlice":true,"useOlapFormatting":false,"showMemberProperties":false,"showEmptyData":true,"defaultHierarchySortName":"asc","showOutdatedDataAlert":false,"showAggregationLabels":true,"showAllFieldsDrillThrough":false,"liveFiltering":false,"showFieldListSearch":false,"validateFormulas":true,"caseSensitiveMembers":false,"simplifyFieldListFolders":false,"validateReportFiles":true,"allowBrowsersCache":false,"showEmptyValues":false,"distinguishNullUndefinedEmpty":false,"useCaptionsInCalculatedValueEditor":false,"readOnly":false,"strictNumbersParsing":false}
Custom data source api
api1 endpoint: /members
api1 request: {"index":"1948574","type":"members","field":{"uniqueName":"filename"},"page":0}
api1 response: {"members":[{"id":"test","value":"test"},{"id":"test 1","value":"test 1"},{"id":"test 2","value":"test 2"}],"sorted":true,"pageTotal":1,"page":0,"nextPageToken":null}
api2 endpoint: /members
api2 request: {"index":"1948574","type":"members","field":{"uniqueName":"operation"},"page":0}
api2 response: {"members":[{"id":"file ops","value":"file ops"},{"id":"test console","value":"test console"}],"sorted":true,"pageTotal":1,"page":0,"nextPageToken":null}
api3 endpoint: /select
api3 request: {"type":"select","index":"1948574","query":{"aggs":{"by":{"rows":[{"uniqueName":"filename"}],"cols":[{"uniqueName":"operation"}]},"values":[{"func":"sum","field":{"uniqueName":"id"}}]}},"querytype":"select","page":0}
api3 response: {"fields":null,"hits":null,"aggs":[{"keys":{},"values":{"id":{"sum":840}}},{"keys":{"filename":"test 2"},"values":{"id":{"sum":239}}},{"keys":{"filename":"test"},"values":{"id":{"sum":245}}},{"keys":{"filename":"test 1"},"values":{"id":{"sum":356}}},{"keys":{"operation":"file ops"},"values":{"id":{"sum":366}}},{"keys":{"operation":"test console"},"values":{"id":{"sum":474}}},{"keys":{"filename":"test 1","operation":"test console"},"values":{"id":{"sum":235}}},{"keys":{"filename":"test 2","operation":"test console"},"values":{"id":{"sum":239}}},{"keys":{"filename":"test 1","operation":"file ops"},"values":{"id":{"sum":121}}},{"keys":{"filename":"test","operation":"file ops"},"values":{"id":{"sum":245}}}],"pageTotal":1,"page":0,"nextPageToken":null}
⋅ 2 days ago
Hello, Jun Jie,
Thank you for sharing the detailed setup and the request-response chain. It allowed us to reproduce the issue on our side.
Empty cells in "differenceofrow" columns
The empty cells are caused by the /select response, which contains no entries for the "test"-"test console" and "test 2"-"file ops" intersections. The differenceofrow function needs a value in both adjacent columns, so for these rows there is simply nothing to calculate. Since your format sets nullValue to an empty string, such cells are displayed as blank.
To resolve this, return the missing intersections explicitly with zero values, for example:
{"keys":{"filename":"test","operation":"test console"},"values":{"id":{"sum":0}}}
Inaccurate difference calculation with "min" aggregation
Flexmonster does not support specifying which aggregation to use when calculating differenceofrow, or other similar aggregations, like percentofrow and runningtotalsofrow. These are client-side aggregations, calculated by the component based on the sum aggregation. When such an aggregation is selected, the server only receives a request for sum and has no knowledge of which aggregation is actually displayed in the pivot table. This is why the difference may seem incorrect in this particular scenario.
As a workaround, we suggest the following options:
id_min) to the dataset and to the response to the /fields request, with min set as its only available aggregation. Since min is the only aggregation the server supports for this field, Flexmonster requests it as the base when calculating differenceofrow, so the difference is calculated between the minimum values: https://jsfiddle.net/flexmonster/4mzn7xdy/Please let us know if our answer helped you.
Best regards,
Maksym
⋅ 2 hours ago
Hi Maksym,
For the "return the missing intersections explicitly with zero values", our expectation is that missing intersection should be assume as 0 if there is no corresponding record.
Is there any way to achieve this without return explicitly returning the records from the custom data source api?