Hi all
My first(Second) post hopefully you guys can help
Firstly I am struggling and relatively new so haven't really hot the Variables under the belt yet; so I apologize for the very long expressions.
The Business Case:
To firstly summarize the business case itself
Trying to calculate a Percentage Revenue Split from Preset set analysis for Telephone Minutes Spend.
There is a total Data bundle package sold that has a complete revenue potential that has usage, that is subtracted monthly from the total potential Revenue.
I have two commission earning entities one that gets a set amount; another that gets 30% off everything that is left off the profit,
as a benefit off being the billing agent.
Base Calculation: (This works fine)
Basically I have a Calculation based on three set analyses that give me an value:
((<//Total Free Minutes Potential Revenue> LESS <//Carbon Cost >LESS <//Dealer Commissions In_Bundle> )) * 30% = <Profit_Share>
Anything within the //Comments as mentioned I haven't managed to get myself into a proper working variable yet but it seems to work ok. except for one issue.
///////////////////START
(Sum({$<[Custom_Field_Values.i_custom_field]={'300'}, value = {'Yes'},[Volume_Discount_Plans.name] = {"FREEDOM*"}>}(RIGHT([Volume_Discount_Plans.name], Len([Volume_Discount_Plans.name])-8)))*0.31
-
//Carbon Cost
Sum({$<[Custom_Field_Values.i_custom_field]={'300'}, value = {'Yes'},i_vendor={32,35,36,39,40},[Services.name]={"Voice Calls"}>}[CDR_Vendors.charged_amount]+(charged_quantity/60)*0.04)
-
//Dealer Commisions In_Bundle
Sum({$<[Custom_Field_Values.i_custom_field]={'300'}, value = {'Yes'},[Volume_Discount_Plans.description] = {"INBUNDLECOMMISSION*"}>}(RIGHT([Volume_Discount_Plans.description], Len([Volume_Discount_Plans.description])-19)))
)*0.3)
///////////////////END
The Problem:
That Calculation on it's own works fine except that occasionally on this business case I end up with a negative values;
This is due to the Sum off <Carbon_Cost> and <Dealer Commissions In_Bundle> Occasionally exceeded.
Now if this happens the is no 'Proverbial Claw Back' <The Profit_Share> should really just be zeroed instead. ie
IF
((<//Total Free Minutes Potential Revenue> LESS <//Carbon Cost >LESS <//Dealer Commissions In_Bundle> )) * 30% <= 0
DONT SUM IT ELSE SUM IT <Profit_Share>
I have managed to accomplish what I need from an IF Statement but this does NOT seem to be very friendly to attach to a bar chart.
Thanking any assistance in advance
The IF Statement In question; that
The IF Statement I would Like to convert into a 'Pure' Set Analysis
IF(
//////EXPRESSION To ANALYSE START
//Total Free Minutes Potential Revenue
(Sum({$<[Custom_Field_Values.i_custom_field]={'300'}, value = {'Yes'},[Volume_Discount_Plans.name] = {"FREEDOM*"}>}(RIGHT([Volume_Discount_Plans.name], Len([Volume_Discount_Plans.name])-8)))*0.31
-
//Carbon Cost
Sum({$<[Custom_Field_Values.i_custom_field]={'300'}, value = {'Yes'},i_vendor={32,35,36,39,40},[Services.name]={"Voice Calls"}>}[CDR_Vendors.charged_amount]+(charged_quantity/60)*0.04)
-
//Dealer Commisions In_Bundle
Sum({$<[Custom_Field_Values.i_custom_field]={'300'}, value = {'Yes'},[Volume_Discount_Plans.description] = {"INBUNDLECOMMISSION*"}>}(RIGHT([Volume_Discount_Plans.description], Len([Volume_Discount_Plans.description])-19)))
)*0.3
///// EXPRESSION To ANALYSE END
< 0, ////LESS THATN ZERO
0, /// MAKEZERO IF LESS THAN ZERO ELSE SUM
(Sum({$<[Custom_Field_Values.i_custom_field]={'300'}, value = {'Yes'},[Volume_Discount_Plans.name] = {"FREEDOM*"}>}(RIGHT([Volume_Discount_Plans.name], Len([Volume_Discount_Plans.name])-8)))*0.31
-
//Carbon Cost
Sum({$<[Custom_Field_Values.i_custom_field]={'300'}, value = {'Yes'},i_vendor={32,35,36,39,40},[Services.name]={"Voice Calls"}>}[CDR_Vendors.charged_amount]+(charged_quantity/60)*0.04)
-
//Dealer Commisions In_Bundle
Sum({$<[Custom_Field_Values.i_custom_field]={'300'}, value = {'Yes'},[Volume_Discount_Plans.description] = {"INBUNDLECOMMISSION*"}>}(RIGHT([Volume_Discount_Plans.description], Len([Volume_Discount_Plans.description])-19)))
)*0.3) ///////ELSE HONOR THE EXPRESSION
