Superset cumulative sum 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 achieve the desired cumulative sum calculation in Superset's Big Number and Table visualizations, especially when dealing with filters from different datasets, you can follow these steps:

1. Big Number Visualization:

   • Use the "Rolling Window" feature to calculate cumulative sums. Set the rolling_type to 'cumsum' in the "Advanced Analytics" section of the control panel ^[1].
   • To handle the filter from a different dataset, you might need to use SQL Lab to create a virtual dataset that combines the necessary columns from both datasets. This can be done by writing a SQL query that joins the datasets on a common key and includes the necessary columns for filtering and calculation.

2. Handling Different Column Names:

   • If the column names differ, ensure that your SQL query in SQL Lab aliases the columns to have consistent names across datasets. This will allow you to use the same filter across different datasets.

3. Table Visualization:

   • Similar to the Big Number visualization, you can use SQL Lab to create a virtual dataset that includes cumulative calculations. Use SQL window functions like SUM() with OVER() to calculate cumulative sums.
   • Ensure that the dataset is structured to include the necessary columns for filtering and cumulative calculations.

4. Dashboard Filters:

   • When using dashboard filters, ensure that the filter values are correctly passed to the SQL query. You can use the filter_values function to dynamically insert filter values into your SQL query.

By creating a virtual dataset in SQL Lab that combines the necessary data and calculations, you can effectively use dashboard filters across different datasets and achieve the desired cumulative calculations in both Big Number and Table visualizations.

To continue talking to Dosu, mention @dosu.

---

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