How to calculate contribution in Revenue by non linked field based on user selection ?

[shekhar_analyti]

Hi All ,

How to calculate contribution in Revenue by non linked field based on user selection ?

• IF USER SELECTS VALUE 1 IN DROP DOWN THEN FROM Top_BU field Bode-Baumbach should be selected , and % of its contribution from total sales be calculated ... Likewise
• IF USER SELECTS VALUE 2 IN DROP DOWN THEN FROM Top_BU field Bode-Baumbach and Cremin Group should be selected ,
• IF USER SELECTS VALUE 3 IN DROP DOWN THEN FROM Top_BU field Bode-Baumbach,Cremin Group and Cummings should be selected
• Similarly for 4 first four should be selected ... and likewise

Please refer the attachment for detail .

Thanks & Regards

Shekar

[kakani87]

Something like this ??

[shekhar_analyti]

Can you please explain the logic and script , I do not have licensed version software ? Screenshots will help

[kakani87]

Did Something like this accordinly what i understood and may be useful for you

Need Dropdown in an input box with the Top BU,s listed in that rite???

Added a Field S,No

For top_Bu

like

S.No, BU

1,   A_BU

2,   B_Bu

.

.

.

and added in script like below with a Flag assigned

Topx:

LOAD Business_Unit,

     Industry,

     Product,

     Sector,

     Market,

     Symbol,

     Department,

     Year,

     Revenue,

     Measure,

     Value,

     Country

FROM

(ooxml, embedded labels, table is data);

join

//Topx

Cl:

LOAD

S.No

,Top_BU as Business_Unit

,'TOP' as Flag

FROM

(ooxml, embedded labels, table is [Top Client]);

Flag as filter to filter top BU

create a variable

vTop_BU

=S.No

Now took Input Box object

Add variable vTop_BU

Now in Constraints Tab

Check Option - Predefined values in Dropdown

and also check option - Listed Values then

enter 1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20 in that

Take a straight Table with dimension Year and Measures as

Column(1)-Total Revenue = Sum(TOTAL{<S.No={'$(vTop_BU)'}>} Revenue)

Column(2)-Revenue = Sum({<S.No={'$(vTop_BU)'}>} Revenue)

Column(3)-Contribution =Column(2)/Column(3) (i.e -  Revenue / Total Revenue )

[kakani87]

[shekhar_analyti]

Hi ,

I wanted that if 5 is selected then first 5 Top_BU values should be selected and corresponding contribution should be depicted from full set of data . If user select 4 then first 4 .. likewise

[shekhar_analyti]

stalwar1‌ .. Sorry on that day , i could not make my requirement clear ..

[sunny_talwar]

Once again the ranking of BUs is not clear... I had to assume the sorting by text A->Z... check attached

[shekhar_analyti]

Hi Sunny Bhai ,

It seems something is not clearly working .

1) I selected 6 but only two Top Bu was selected .. however ever top 6 bu should have been selected  .

2) Also the contribution of selected BU to sales

Thanks & Regards

Shekar

[prahlad_infy]

Helo Sekar ,

Thank you for posting question . I too same requirement .
You posting good question .

[sunny_talwar · Accepted solution]

Here, I changed the expression for trigger to this

='=Count({1}DISTINCT TOTAL Top_BU) - Rank(Only({1}Top_BU)) + 1 <= vTop_X_BU'