How to calculate the total sum of a field in beast Mode
How can I calculate the total sum for a given row based on the condition. e.g.
for every G_Account No. I will like to calculate total Amount, for Transaction = 0 and Transaction = 1 aggregated by year:
I have used the formulala:
sum(sum(`Amount`)) over (partition by year(`Posting Date`)), however it correctly calculates for the year, but sums up both for Transaction 1 & 0.
Thank you
Answers
-
I think you'd need to do that with two separate beast modes; one like this:
case when Transaction = 1 then sum(sum(`Amount`)) over (partition by year(`Posting Date`)) end
and one like this:
case when Transaction = 0 then sum(sum(`Amount`)) over (partition by year(`Posting Date`)) end
You may need a third for the total year, but you have the formula for that already.
0 -
Hi @msharma since you’re wanting to do a conditional sum you need to use a case statement inside your sum to only select the values you want
sum(sum(case when `Transaction` = 1 then `Amount` else 0 end)) over (partition by year(`Posting Date`))
Repeat for transaction 0
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**3 -
Thank you. It works.
I have a follow up question.
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
Thank you
0 -
You wouldn't be able to do that on the card because the filter happens before any beast mode is applied. You'd need to aggregate your data in an ETL before hand.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
Hello, I want to do kind of the same operation but more for Type 0 to Type 70 for example.
In the Table card, I want to see first ordered by Country and then Month. What Should I do?
(I tried two-time order but gave me a syntax error)
Now my table uses the following code in beast mode
COUNT(`Mktg` in 'Yes'),1) over(PARTITION by `Country` order by MONTH(`Date`)
is
Type Month Measure 1
Type0 April 100
Type1 April 92
Type2 April 90
:
Type 70 April 60
Type0 May 75
Type1 May 93
Type2 May 99
:
&
The Table I want should be:
Type Month Measure 1
Type0 April 100
Type0 May 92
Type0 June 80
:
Type0 Dec 80
Type 1
:
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 620 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 742 Beast Mode
- 58 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 124 Manage
- 121 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive