Good Day,
Im trying to get a ratio percentage based on the Transaction total, but when do an expression calculation to get the perecentage it give me the wrong figure and the expression is as follows based on the script below :
=Sum({$<CalendarMonth={"$(=$(vCommPreviousMonth))"},TransactionType={"V2Vaps","V5Vaps"}>} TransactionTotal)
/
Sum({$<CalendarMonth={"$(=$(vCommPreviousMonth))"},TransactionType={"V2Contracts","V5Contracts","V5ContractsMFC","V5ContractsWESBANK"}>} TransactionTotal)
vPreviousMonth=date(MonthName(addmonths(today(),-1)), 'MMM');
QUALIFY *;
"V5Deal":
LOAD id as deal_id,
dealstate_id,
dealgroup_id,
Date(date_created,'YYYY-MM-DD hh:mm:ss') as date_created,
Date(date_updated,'YYYY-MM-DD hh:mm:ss') as date_updated
FROM
[$(vQvdDirectory)v5_Wesbank_deal_2016.QVD](qvd)
where dealstate_id = '71'
//and Year(date_updated) = '2016';
and Year(date_updated) = $(vCurrentYear);
UNQUALIFY *;
"temp_Deal":
NoConcatenate
Load V5Deal.deal_id,
V5Deal.dealstate_id,
V5Deal.dealgroup_id,
V5Deal.date_created,
V5Deal.date_updated
Resident "V5Deal"
order by V5Deal.deal_id,V5Deal.date_updated desc;
DROP Table "V5Deal";
"V5Deal":
NoConcatenate
Load V5Deal.deal_id,
V5Deal.dealstate_id,
V5Deal.dealgroup_id,
V5Deal.date_created,
V5Deal.date_updated
Resident "temp_Deal"
Where V5Deal.deal_id<>Previous(V5Deal.deal_id);
drop Table "temp_Deal";
"V5_ContractsPaidTMP":
LOAD V5Deal.deal_id,
V5Deal.dealstate_id,
V5Deal.dealgroup_id as dealgroup_id,
V5Deal.date_created,
V5Deal.date_updated
Resident "V5Deal";
left Join (V5_ContractsPaidTMP)
load dealgroup_id,
Dealer.id,
Dealgroup.member_id
Resident Dealgroup;
Left Join(V5_ContractsPaidTMP)
LOAD first_name & ' ' &last_name as ConsultantName,
institution_id as Dealer.id;
SQL SELECT `first_name`,
`last_name`,
`institution_id`
FROM VAF5.member
left join VAF5.relationship on VAF5.relationship.member_id = VAF5.member.id
left join VAF5.relationshiptype on VAF5.relationship.relationshiptype_id = VAF5.relationshiptype.id
where VAF5.relationship.relationshiptype_id = 6
and VAF5.relationship.status_id = 2;
DROP Table "V5Deal";
"V5_ContractsPaidTMP2":
LOAD ConsultantName,
V5Deal.deal_id as deal_id,
V5Deal.date_created as date_created,
V5Deal.date_updated as date_updated
Resident V5_ContractsPaidTMP;
DROP table V5_ContractsPaidTMP;
"V5_ContractsPaidTMP3":
LOAD ConsultantName,
deal_id,
Day(date_updated) as CalendarDay,
Month(date_updated) as CalendarMonth,
Year(date_updated) as CalendarYear
Resident V5_ContractsPaidTMP2;
drop Table V5_ContractsPaidTMP2;
"V5_ContractsPaidTMP4":
NoConcatenate
LOAD ConsultantName,
deal_id,
CalendarDay,
date(Date#(CalendarMonth, 'MMM'), 'MMM')as CalendarMonth,
CalendarYear,
'V5Contracts' as TransactionType
Resident V5_ContractsPaidTMP3
where CalendarMonth > date(Date#('$(vStartMonth)', 'MMM'), 'MMM');
DROP Table V5_ContractsPaidTMP3;
//"V5_ContractsPaidTMP5":
Concatenate(V5_ContractsPaidTMP5)
LOAD ConsultantName,
CalendarDay,
CalendarMonth,
CalendarYear,
Count(DISTINCT deal_id) as tempV5ContractsPaid
Resident V5_ContractsPaidTMP4
Group by ConsultantName,CalendarDay,CalendarMonth,CalendarYear ;
DROP Table V5_ContractsPaidTMP4;
"V5_ContractsPaid":
Concatenate (NumberExcelTransactions)
LOAD ConsultantName,
CalendarDay,
CalendarMonth,
CalendarYear,
tempV5ContractsPaid as TransactionTotal,
'V5ContractsWESBANK' as TransactionType
Resident V5_ContractsPaidTMP5;
drop Table V5_ContractsPaidTMP5;
ConsultantName:
LOAD [Consultant Name] as ConsultantName
FROM
(biff, embedded labels, table is Sheet1$);
Inner join(ConsultantName)
LOAD *
Resident NumberExcelTransactions;
DROP Table NumberExcelTransactions;
RENAME Table ConsultantName to NumberExcelTransactions;
