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
