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

LucaMartini1969
LucaMartini1969 Member
in Workbench

Deal All, I have prepared a P&L summary with below aspect

image.png

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

image.png

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

image.png

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

  • ST_Superman
    ST_Superman Domo Employee

    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
    LucaMartini1969 Member

    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
    LucaMartini1969 Member

    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?

  • MichelleH
    MichelleH Coach

    @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?

Categories