Ratio calculations

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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;

topic Ratio calculations in QlikView

Ratio calculations