Superset cumulative sum problem in Big Number and Table.

[Shabaz86]

Hi , I have a problem in both superset Big Number and Table.
for the Big number: What I need is I want to calculate the Till the month base but the issue is I want to pass this month from a selected value in dashboard filter (but this is on different dataset and name of the column is also not the same). so that I'm not able to do.
Till now what I was trying that I joined the two datasets on ID but issue in this I select a value in filter then dataset itself will filtered and then it will count the base till date in that filtered dataset.
max("onboarding month datetime") <= {% if filter_values("transaction month datetime")|length %} {{ filter_values("transaction month datetime")[0] }} {% else %} extract(epoch from current_date) {% endif %}

the same I want in table where I get cumulative base IDs onboarded till each month column in table.

Please help me on this , Thanks.

[dosubot[bot]]

To address your issue with calculating cumulative sums in Superset's Big Number and Table visualizations, especially when dealing with different datasets and column names, you can follow these steps:

1. Use Jinja Templating for Dynamic Filtering: You can use Jinja templating to dynamically reference the filter values from your dashboard. Since the column names differ between datasets, you can use the filter_values function to retrieve the selected filter value and apply it in your query.

2. Modify Your Query: Adjust your query to ensure it correctly references the filter value. Here's an example of how you might structure your query using Jinja templating:

   SELECT 
       SUM(CASE WHEN "onboarding month datetime" <= 
           {% if filter_values("transaction month datetime")|length %}
               {{ filter_values("transaction month datetime")[0] }}
           {% else %}
               extract(epoch from current_date)
           {% endif %}
       THEN 1 ELSE 0 END) AS cumulative_count
   FROM your_table

   This query calculates the cumulative count of IDs onboarded up to the selected month.

3. Ensure Correct Dataset Joins: If you need to join datasets, ensure that the join is done in a way that doesn't filter out necessary data before the cumulative calculation. You might need to perform the join in a subquery or use a common table expression (CTE) to maintain the full dataset for cumulative calculations.

4. Implement in Table Visualization: For the Table visualization, you can use a similar approach. Ensure that your query includes a cumulative calculation for each month. You might need to use window functions like SUM() OVER (PARTITION BY ...) to achieve this.

By using these techniques, you should be able to calculate cumulative sums based on a selected month from a dashboard filter, even when dealing with different datasets and column names.

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Other}

[Shabaz86]

@dosu we have already tried the below technique but it does not take the value of transaction month datetime from the dataset. as the column name is different and when filtering it does not refreshes.

[dosubot[bot]]

To address the issue of using a filter from a different dataset with a different column name, you might consider the following approach:

1. Cross-Filtering with Aliases: If Superset does not natively support cross-filtering between datasets with different column names, you can create a virtual dataset or view in your database that aligns the schemas. This involves creating a view where the column names are aliased to match each other, allowing the filter to apply correctly.

2. Custom SQL Queries: Use a custom SQL query in Superset to join the datasets and alias the columns as needed. This way, you can ensure that the filter values are correctly applied across datasets. For example:

   SELECT 
       a.id,
       a."onboarding month datetime",
       b."transaction month datetime" AS "onboarding month datetime"
   FROM dataset_a a
   JOIN dataset_b b ON a.id = b.id

   This query aligns the column names, allowing the filter to work as intended.

3. Dashboard Filter Synchronization: Ensure that the dashboard filters are set up to apply to the correct datasets. You may need to configure the filter scope to include all relevant charts and datasets.

4. Check Filter Application: Verify that the filter is correctly applied by checking the filter configuration in the dashboard. Ensure that the filter is set to affect all necessary datasets and visualizations.

These steps should help in ensuring that the filter values are correctly applied, even when dealing with different datasets and column names.

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Other}