create measure in script Load

[RimDataAnalyst]

Hello everyone ,

i have 4 measures to calculate in the Loading script : 

pricing , K revenue , B revenue and G revenue , i have the formula for pricing , K revenue , B revenue  and G revenue is pricing - K revenue - B revenue

here is it the script :

sum(distinct((([Value Generation (k$/Tail/Yr)] * $(v_Customer_value_portion)/100 * $(v_Performance)/100 )+
((0.357 *[Value Generation (k$/Tail/Yr)] * $(v_Customer_value_portion)/100+[Platform Fee])))* (1-$(v_Discount)/100)
*(13-month(AddMonths( Contract_Start_Date_real, $(v_NO_EA_Onboarding_phase) + $(v_NO_EA_Trial_period) )) +36)/12
))* count(distinct(MSN)) AS pricing ,

if( Partner ='K',((sum(distinct [Value_by_partner]*[Value Generation (k$/Tail/Yr)]* (13-month(AddMonths( Contract_Start_Date_real, $(v_NO_EA_Onboarding_phase) + $(v_NO_EA_Trial_period) )) +36)/12 )
* $(v_Customer_value_portion)/100 * $(v_Performance)/100 * (1-$(v_Discount)/100))
+ sum(distinct [Value Generation (k$/Tail/Yr)]
*(13-month(AddMonths( Contract_Start_Date_real, $(v_NO_EA_Onboarding_phase) + $(v_NO_EA_Trial_period) )) +36)/12
)* $(v_Customer_value_portion)/100 * $(v_DAL_contribution)/100 * (1-$(v_Discount)/100)))
AS K_revenus,

if(Partner ='B', sum(distinct [Value_by_partner]*[Value Generation (k$/Tail/Yr)])
*$(v_Performance)/100 * (1-$(v_Discount)/100)* $(v_Customer_value_portion)/100)
* (13-month(AddMonths( Contract_Start_Date_real, $(v_NO_EA_Onboarding_phase) + $(v_NO_EA_Trial_period) )) +36)/12
AS B_revenus

then when i add pricing - K revenue - B revenue AS G revenue it doesn't work 

So I created another table to only do the calcul of G revenue but it gives me 0 every time 

Thank you 

[deepanshuSh]

First of all, are you using the correct group by for the calculation. Second, for getting the exact reason, check if the initial values are there on front end (K revenue and  B revenue) or not, and if the primary key that is the group by field is having values at the granular level or not. 

[vinieme12]

can you post the entire load script?

[RimDataAnalyst]

this is the loading script 

unqualify *;
fact_rewards_tmp:
Load Distinct

[Product - Name], ICAO, [AC Type],Conf,MSN,[Customer profile], Partner ,Contract_Start_Date_real,Contract_End_Date,Value_by_partner,[Value Generation (k$/Tail/Yr)], [Platform Fee],
[End Date], [Start Date],
sum(distinct((([Value Generation (k$/Tail/Yr)] * $(v_Customer_value_portion)/100 * $(v_Performance)/100 )+
((0.357 *[Value Generation (k$/Tail/Yr)] * $(v_Customer_value_portion)/100+[Platform Fee])))* (1-$(v_Discount)/100)
*(13-month(AddMonths( Contract_Start_Date_real, $(v_NO_EA_Onboarding_phase) + $(v_NO_EA_Trial_period) )) +36)/12
))* count(distinct(MSN)) AS pricing ,

if( Partner ='k',((sum(distinct [Value_by_partner]*[Value Generation (k$/Tail/Yr)]* (13-month(AddMonths( Contract_Start_Date_real, $(v_NO_EA_Onboarding_phase) + $(v_NO_EA_Trial_period) )) +36)/12 )
* $(v_Customer_value_portion)/100 * $(v_Performance)/100 * (1-$(v_Discount)/100))
+ sum(distinct [Value Generation (k$/Tail/Yr)]
*(13-month(AddMonths( Contract_Start_Date_real, $(v_NO_EA_Onboarding_phase) + $(v_NO_EA_Trial_period) )) +36)/12
)* $(v_Customer_value_portion)/100 * $(v_DAL_contribution)/100 * (1-$(v_Discount)/100)))
AS K_revenus,

if(Partner ='B', sum(distinct [Value_by_partner]*[Value Generation (k$/Tail/Yr)])
*$(v_Performance)/100 * (1-$(v_Discount)/100)* $(v_Customer_value_portion)/100)
* (13-month(AddMonths( Contract_Start_Date_real, $(v_NO_EA_Onboarding_phase) + $(v_NO_EA_Trial_period) )) +36)/12
AS B_revenus

Resident prospection_oi_sales_calc
group by [Product - Name], ICAO, [AC Type],Conf,MSN,[Customer profile], Partner ,Contract_Start_Date_real,
Value_by_partner,[Value Generation (k$/Tail/Yr)], [Platform Fee]
,Contract_End_Date,[End Date], [Start Date]
//AutoNumber(Month(Contract_Start_Date_real) &'|'& year(Contract_Start_Date_real))
;

/////////////////////////////////////////////////////////////// Calcul G rewards

unqualify *;
fact_rewards_tmp2:
Load Distinct
[Product - Name], ICAO, [AC Type],Conf,MSN,[Customer profile], Partner ,Contract_Start_Date_real,Contract_End_Date,Value_by_partner,[Value Generation (k$/Tail/Yr)], [Platform Fee],
[End Date], [Start Date],pricing,B_revenus,K_revenus,
sum( pricing- B_revenus-K_revenus )AS G_revenus

Resident fact_rewards_tmp
group by [Product - Name], ICAO, [AC Type],Conf,MSN,[Customer profile], Partner ,Contract_Start_Date_real,Contract_End_Date,Value_by_partner,[Value Generation (k$/Tail/Yr)], [Platform Fee],
[End Date], [Start Date],pricing,B_revenus,K_revenus

[vinieme12]

Are these chart expressions that you are trying to calculate in script?

Break your calculations into parts to verify which block is failing to evaluate , any +-*/ with a null value will result in a null value

unqualify *;

let v_Performance = ?? ; 
let v_Customer_value_portion= ??;
let v_Discount = ?? ;
let v_NO_EA_Onboarding_phase = ?? ;
let v_NO_EA_Trial_period = ?? ;
let v_DAL_contribution = ??;

fact_rewards_tmp:
Load Distinct

[Product - Name]
, ICAO
, [AC Type]
,Conf
,MSN
,[Customer profile]
, Partner 
,Contract_Start_Date_real
,Contract_End_Date
,Value_by_partner
,[Value Generation (k$/Tail/Yr)]
, [Platform Fee]
,[End Date]
, [Start Date]
, ([Value Generation (k$/Tail/Yr)] * $(v_Customer_value_portion)/100 * $(v_Performance)/100 ) as part1 
,(0.357 *[Value Generation (k$/Tail/Yr)] * $(v_Customer_value_portion)/100+[Platform Fee])  as part2
,(1-$(v_Discount)/100) as part3
,(13-month(AddMonths( Contract_Start_Date_real, $(v_NO_EA_Onboarding_phase) + $(v_NO_EA_Trial_period) )) +36)/12 as part4
, count(MSN) AS part5


Resident prospection_oi_sales_calc
group by 
[Product - Name]
, ICAO
, [AC Type]
,Conf
,MSN
,[Customer profile]
, Partner 
,Contract_Start_Date_real
,Contract_End_Date
,Value_by_partner
,[Value Generation (k$/Tail/Yr)]
, [Platform Fee]
,[End Date]
, [Start Date]
;

Qualify *;

test:
Load 
Distinct 
[Product - Name]
, ICAO
, [AC Type]
,Conf
,MSN
,[Customer profile]
, Partner 
,Contract_Start_Date_real
,Contract_End_Date
,Value_by_partner
,[Value Generation (k$/Tail/Yr)]
, [Platform Fee]
,[End Date]
, [Start Date]
,sum((part1+part2)*part3*part4*part5) as pricing
Resident fact_rewards_tmp
group by 
[Product - Name]
, ICAO
, [AC Type]
,Conf
,MSN
,[Customer profile]
, Partner 
,Contract_Start_Date_real
,Contract_End_Date
,Value_by_partner
,[Value Generation (k$/Tail/Yr)]
, [Platform Fee]
,[End Date]
, [Start Date]
;

[RimDataAnalyst]

thank you for the answer but this doesn't work