Partition By not working to show correct SM% when I change parameters view options

LucaMartini1969

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

ST_-Superman-_

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.

LucaMartini1969

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' 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

ELSE 1
END

Geo/Prod view 2" is:

Case

when Geography/Product View = 'Geography' then Zone
when Geography/Product View = 'Product' then Business

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

LucaMartini1969

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?