Partition By not working to show correct SM% when I change parameters view options
Deal All, I have prepared a P&L summary with below aspect
When I change the filter in the "Geography/Product View" and for example I switch from "P&L" to "Geography", I am not able tom show the proper SM% by Geography, I see the same % for the different countries in the same quarter, see below
As Column I am showing the Fiscal Quarter and I am using below specific formula to calculate the SM %:
WHEN P&L Account name
='SM Act%' THEN
CASE WHEN SUM(SUM(CASE WHEN P&L Account name
='SM Act' THEN SM Act
ELSE 0 END)) OVER (PARTITION BY FISCL_QTR
,'Geo/Prod view 1','Geo/Prod view 2','Geo/Prod view 3','Geo/Prod view 4')/SUM(SUM(CASE WHEN P&L Account name
='Sales Act' THEN Sales Act
ELSE 0 END)) OVER (PARTITION BY FISCL_QTR
,'Geo/Prod view 1','Geo/Prod view 2','Geo/Prod view 3','Geo/Prod view 4') >=0 THEN
CONCAT( ROUND(SUM(SUM(CASE WHEN P&L Account name
='SM Act' THEN SM Act
ELSE 0 END)) OVER (PARTITION BY FISCL_QTR
,'Geo/Prod view 1','Geo/Prod view 2','Geo/Prod view 3','Geo/Prod view 4')/SUM(SUM(CASE WHEN P&L Account name
='Sales Act' THEN Sales Act
ELSE 0 END)) OVER (PARTITION BY FISCL_QTR
,'Geo/Prod view 1','Geo/Prod view 2','Geo/Prod view 3','Geo/Prod view 4')*100,1),'%')
ELSE
CONCAT('(', ROUND(SUM(SUM(CASE WHEN P&L Account name
='SM Act' THEN SM Act
ELSE 0 END)) OVER (PARTITION BY FISCL_QTR
,'Geo/Prod view 1','Geo/Prod view 2','Geo/Prod view 3','Geo/Prod view 4')/SUM(SUM(CASE WHEN P&L Account name
='Sales Act' THEN Sales Act
ELSE 0 END)) OVER (PARTITION BY FISCL_QTR
,'Geo/Prod view 1','Geo/Prod view 2','Geo/Prod view 3','Geo/Prod view 4')*100,1),'%)') END
In Dataset I have :
one column called "P&L Account Name" with the P&L parameters: Sales Act, SM Act, SM%
one column called "Sales Act" to show values for Sales
one column called "SM Act" to show values for Sales Margin
On top, I created below variable to choose different option view
The issue is with the Partition By parameters, below parameters are not working in the Beast Mode:
(PARTITION BY FISCL_QTR
,'Geo/Prod view 1','Geo/Prod view 2','Geo/Prod view 3','Geo/Prod view 4')
where "Fiscl_QTR" is the quarter
where "Geo/Prod view 1" is:
Case
when Geography/Product View
= 'Geography' then P&L Account name
when Geography/Product View
= 'Product' then P&L Account name
when Geography/Product View
= 'P&L' then P&L Account name
END
where "Geo/Prod view 2" is:
Case
when Geography/Product View
= 'Geography' then Zone
when Geography/Product View
= 'Product' then Business
END
where "Geo/Prod view 3" is:
Case
when Geography/Product View
= 'Geography' then LCT
when Geography/Product View
= 'Product' then Sub Mod 2
END
where "Geo/Prod view 4" is:
Case
when Geography/Product View
= 'Geography' then Country
when Geography/Product View
= 'Product' then Sub Mod 1
END
Could you please help me to fix the Partiton parameters or do you suggest some other alternatives in order to show correct SM% for each country and each quarter?
Regards
Luca
Answers
-
Your case statements don't have any clause for when Geography/Product View = 'P&L'
Either add an else 1 at then end of each case statement, or call out a specific value for each option that the user can select.
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman2 -
Hi ST Superman, so if I understood correctly as first option you are suggesting to modify my formula like below
where "Geo/Prod view 1" is:
Case
when
Geography/Product View
= 'Geography' thenP&L Account name
whenGeography/Product View
= 'Product' thenP&L Account name
whenGeography/Product View
= 'P&L' thenP&L Account name
ELSE 1
ENDGeo/Prod view 2" is:
Case
when
Geography/Product View
= 'Geography' thenZone
whenGeography/Product View
= 'Product' thenBusiness
ELSE 1
END
etc
I didn't understand second option "or call out a specific value for each option that the user can select."
coul you show me an example modifying my formula?
Thx a lot for your help!!!!
Luca
0 -
I tried the suggested solutiion, adding the ELSE 1, but unfortunately the SM% is not working, it is still showing same %. The is is whith PARTTION BY, do you know some way to fix PARTITION BY issue?
0 -
@LucaMartini1969 Just to be sure, is that exactly how you have the partition written in your beast mode or are you simplifying for us to view? If you are simplifying it, could you please share the full formula?
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 297 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 678 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 46 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive