Time Table Comparison & Comparison Time Filter for Dashboard

[hamrio]

Hi,

I try the chart plugin experimental (Time Comparison Table). When I see the query after the chart is built, I see the chart is only use left outer join instead of full outer join. The query will lead to miss some data if the compare value has more dimension than the current value. or is that a reason to use left join instead full outer join?

Next question is about comparison time filter on the dashboard. I see the new table and it is good. I see the chart will be experimental release in 4.1.0rc. I think we also need new time filter for the dashboard because there will be chart have 2 time filter.

Thank you

[dosubot[bot]]

Hey @hamrio! I'm a bot here to help you with any issues, questions, or contributions. Feel free to ask for assistance!

The rationale behind using a left outer join instead of a full outer join in the generated query for the experimental Time Comparison Table chart plugin is to ensure that all rows from the main dataset are included in the result, even if there are no matching rows in the comparison dataset. This approach helps maintain the integrity of the main dataset while still allowing for comparison data to be included where available ^[1].

To modify the join type to use a full outer join, you need to adjust the timeCompareOperator function call within the buildQuery function. Specifically, you need to ensure that the join type is set to full_outer when constructing the post-processing rules. Here is an example of how to do this:

import { timeCompareOperator, TimeCompareJoinType } from '@superset-ui/chart-controls';

export default function buildQuery(formData: QueryFormData) {
  const baseFormData = {
    ...formData,
  };

  const formData1 = removeFormDataSuffix(baseFormData, '_b');
  const formData2 = retainFormDataSuffix(baseFormData, '_b');

  const queryContexts = [formData1, formData2].map(fd =>
    buildQueryContext(fd, baseQueryObject => {
      const queryObject = {
        ...baseQueryObject,
        columns: [
          ...(isXAxisSet(formData)
            ? ensureIsArray(getXAxisColumn(formData))
            : []),
          ...ensureIsArray(fd.groupby),
        ],
        series_columns: fd.groupby,
        ...(isXAxisSet(formData) ? {} : { is_timeseries: true }),
      };

      const pivotOperatorInRuntime: PostProcessingPivot = isTimeComparison(
        fd,
        queryObject,
      )
        ? timeComparePivotOperator(fd, queryObject)
        : pivotOperator(fd, queryObject);

      const tmpQueryObject = {
        ...queryObject,
        time_offsets: isTimeComparison(fd, queryObject) ? fd.time_compare : [],
        post_processing: [
          pivotOperatorInRuntime,
          rollingWindowOperator(fd, queryObject),
          timeCompareOperator(fd, queryObject, TimeCompareJoinType.FullOuter),
          resampleOperator(fd, queryObject),
          renameOperator(fd, queryObject),
          flattenOperator(fd, queryObject),
        ],
      } as QueryObject;
      return [normalizeOrderBy(tmpQueryObject)];
    }),
  );

  return {
    ...queryContexts[0],
    queries: [...queryContexts[0].queries, ...queryContexts[1].queries],
  };
}

Regarding the comparison time filter on the dashboard, the current implementation works by overriding the time range in the query filters if extra form data is provided. This functionality is defined in the getComparisonFilters function located in superset-frontend/packages/superset-ui-core/src/time-comparison/getComparisonFilters.ts ^[2].

There are ongoing updates in the upcoming 4.1.0rc release that address the need for multiple time filters on the dashboard. Specifically, the following pull request highlights progress in this area:

• feat(native-filters): Time native filter (#12992)

Additionally, the new release includes tests for creating and applying time grain and time range filters, as seen in the nativeFilters.test.ts file ^[3][4].

To continue talking to Dosu, mention @dosu.

[hamrio]

@dosu

I don't think the time filter related. My question, is there any update regarding datetime selection filter on the dashboard for comparison time table?

[dosubot[bot]]

There are no specific updates or ongoing discussions related to implementing a datetime selection filter specifically for the Time Comparison Table chart plugin. The current implementation handles datetime filtering through the existing time range filter in native filters, which interacts with the Time Comparison Table chart plugin by determining the time range for the data being visualized ^[1][2][3].

If you need a custom solution, users have implemented workarounds such as using the handleContextMenu function to create filters based on selected datetime values ^[3].

To continue talking to Dosu, mention @dosu.

[luissebastian953]

@dosu in superset v5, how to add time comparison table? When I see the query after the chart is built, I see the chart is still only use left outer join instead of full outer join? any changes and how to apply it?