How to override the filter set on cards and calculate sum on a specified period and condition
How to add hardcoded dates that the amount is summed for a period specified, even when the chard is filtered on for different dates.
e.g. I will like to always get the sum of transactions Type 1 between the period of Jan-01-2020 and Dec-31-2021, even when the filter on the card is set for Jan-01-2021 and Feb-28-2021
As seen in the image, the Budget_Total also gets filtered based on the filter applied to chart.
I have used the following formula in Beast Mode to capture Budget Total:
sum(sum(case when `Transaction Type` = 1 then `Amount` else 0 end)) over (partition by `G_L Account No_`)
Also if possible I will prefer to have a single entry for every 'G_L Account No_', not a repetition.
The filter is applied before any beast mode is calculated so if the card is filtered for Jan&Feb 2021 you won't have access to the 2020 data points. If you need that data point then you'd likely need to calculate it in a dataflow before hand.
As for your G_L_ACCOUNT No_ field - you have different values for the amount, which value are you wanting to have for the amount? Because there's different values you have different rows. You could aggregate the data using either min / max / avg / sum etc to calculate the amount you're looking for (I'm assuming SUM in your case).**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0
@msharma think of analyzer as an engine for writing a SQL query.
When you add filters, you're adding a WHERE clause.
Your Beast Modes are part of the SELECT clause.
in SQL your execution order is FROM - WHERE - GROUP BY - SELECT - ORDER BY.
Same applies in Analyzer.Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0
Thanks for the clarification.
I tried to create a new table in DOMO SQL using the following syntax in sql :
SELECT `G_L Account No_`,`AcctLen`,`fund#`, `Fund No_`,`Dept#`, `Obj#`, `Global Dimension 1 Code`, `Posting Date`, `Description`, `Amount`, `Transaction Type`, `Actual_Amt`, `Budget_Amt`,
sum(sum(case when `Transaction Type` = 1 then `Amount` else 0 end)) over (partition by `G_L Account No_`) as NewSum
I get this error:
where as when I use Group BY I get no error.
SELECT `G_L Account No_`, sum(case when `Transaction Type` = 1 then `Amount` else 0 end) as NewSum
Is Partition by not supported in DOMO SQL?
- 7.6K All Categories
- 913 Connectors
- 241 Workbench
- 470 Transform
- 1.8K Magic ETL
- 60 SQL DataFlows
- 445 Datasets
- 24 Visualize
- 194 Beast Mode
- 2K Charting
- 6 Variables
- 14 Cards, Dashboards, Stories
- 1 Automate
- 348 APIs & Domo Developer
- 82 Apps
- 14 Predict
- 3 Jupyter Workspaces
- 11 R & Python Tiles
- 241 Distribute
- 59 Domo Everywhere
- 241 Scheduled Reports
- 14 Manage
- 35 Governance & Security
- 18 Product Ideas
- 1.1K Ideas Exchange
- Community Forums
- 15 Getting Started
- 1 Community Member Introductions
- 49 Community News
- 18 Event Recordings
- 579 日本支部