Re: C/S RATIO & PERCENTAGE - Qlik Community

sagaraperera · ‎2015-05-05

how to create a c/s ratio and break even sales value & volume at qlick view interface. i have a qlick view p & l file, and i want create a percentage compared with with last month figure

please help

sagara

Gysbert_Wassenaar · ‎2015-05-05

Can you post a source file with data and an excel file with the result you want to achieve, including the calculations?

talk is cheap, supply exceeds demand

sagaraperera · ‎2015-05-05

I HAVE A QVD FILE CAN U HELP SOLVE THE MATTER, MY SCRIPT GIVEN BELOW

Directory;

CrossTable(DESCRIPTION,BUDGET, 6)

LOAD BCOD,

MONTH,

YEAR,

MONTH2,

CLA_CODE,

MONM,

GROSS_PREMIUM,

SRCC_PREMIUM,

REINSURANCE_FUND,

FOREIGN_COMPANIES,

CEEDED_PREMIUM,

NRP,

UPR_CHARGE,

NEP,

SRCC_TC_COMMISSION,

FOREIGN_CO_COMMISSION,

COMMISSION_INCOME,

NET_REVENUE,

GROSS_CLAIMS,

CLAIMS_RES_FUND,

EXGRATIA_CLIMS_RES_FUND,

CLAIMS_PROCESSING_EXPENSES,

LEGAL_FEES,

RI_RECOVERIES_RI_FUND,

RI_RECOVERIES_SRCC_TC,

RI_RECOVERIES_FOREIGN,

SALVAGE_RECOVERIES,

NET_CLAIMS,

COMMISSION_EXPENSES,

PRODUCTION_BONUS,

DAC_CHARGE,

ACQUISITION_COST,

GROSS_PROFIT,

STAFF_EXPENSES,

ADMIN_EXPENSES,

SELLING_EXPENSES,

FINANCIAL_EXPENSES,

DEPRICIATION_EXPENSES,

MANAGEMENT_EXPENSES,

INV_INCOME_EXT,

INV_INCOME_INT,

OTHER_INCOME_EXT,

OTHER_INCOME_INT,

INV_OTHER_INCOME,

NET_PROFIT

FROM

[..\..\qlickviwe\P&L\P&LBUDDATA2011_2014.QVD]

(qvd);

CrossTable(DESCRIPTION, BUDGET, 6)

LOAD BCOD,

MONTH,

YEAR,

MONTH2,

CLA_CODE,

MONM,

GROSS_PREMIUM,

SRCC_PREMIUM,

REINSURANCE_FUND,

FOREIGN_COMPANIES,

CEEDED_PREMIUM,

NRP,

UPR_CHARGE,

NEP,

SRCC_TC_COMMISSION,

FOREIGN_CO_COMMISSION,

COMMISSION_INCOME,

NET_REVENUE,

GROSS_CLAIMS,

CLAIMS_RES_FUND,

EXGRATIA_CLIMS_RES_FUND,

CLAIMS_PROCESSING_EXPENSES,

LEGAL_FEES,

RI_RECOVERIES_RI_FUND,

RI_RECOVERIES_SRCC_TC,

RI_RECOVERIES_FOREIGN,

SALVAGE_RECOVERIES,

NET_CLAIMS,

COMMISSION_EXPENSES,

PRODUCTION_BONUS,

DAC_CHARGE,

ACQUISITION_COST,

GROSS_PROFIT,

STAFF_EXPENSES,

ADMIN_EXPENSES,

SELLING_EXPENSES,

FINANCIAL_EXPENSES,

DEPRICIATION_EXPENSES,

MANAGEMENT_EXPENSES,

INV_INCOME_EXT,

INV_INCOME_INT,

OTHER_INCOME_EXT,

OTHER_INCOME_INT,

INV_OTHER_INCOME,

NET_PROFIT

FROM

[..\..\qlickviwe\P&L\P&LBUDDATA2015.QVD]

(qvd);

CrossTable(DESCRIPTION, Data, 6)

LOAD BCOD,

MONTH,

YEAR,

MONTH2,

CLA_CODE,

MONM,

GROSS_PREMIUM,

SRCC_PREMIUM,

REINSURANCE_FUND,

FOREIGN_COMPANIES,

CEEDED_PREMIUM,

NRP,

UPR_CHARGE,

NEP,

SRCC_TC_COMMISSION,

FOREIGN_CO_COMMISSION,

COMMISSION_INCOME,

NET_REVENUE,

GROSS_CLAIMS,

CLAIMS_RES_FUND,

EXGRATIA_CLIMS_RES_FUND,

CLAIMS_PROCESSING_EXPENSES,

LEGAL_FEES,

RI_RECOVERIES_RI_FUND,

RI_RECOVERIES_SRCC_TC,

RI_RECOVERIES_FOREIGN,

SALVAGE_RECOVERIES,

NET_CLAIMS,

COMMISSION_EXPENSES,

PRODUCTION_BONUS,

DAC_CHARGE,

ACQUISITION_COST,

GROSS_PROFIT,

STAFF_EXPENSES,

ADMIN_EXPENSES,

SELLING_EXPENSES,

FINANCIAL_EXPENSES,

DEPRICIATION_EXPENSES,

MANAGEMENT_EXPENSES,

INV_INCOME_EXT,

INV_INCOME_INT,

OTHER_INCOME_EXT,

OTHER_INCOME_INT,

INV_OTHER_INCOME,

NET_PROFIT

FROM

[..\..\qlickviwe\P&L\P&LDATA2011_2014.QVD]

(qvd);

CrossTable(DESCRIPTION, Data, 6)

LOAD BCOD,

MONTH,

YEAR,

MONTH2,

CLA_CODE,

MONM,

GROSS_PREMIUM,

SRCC_PREMIUM,

REINSURANCE_FUND,

FOREIGN_COMPANIES,

CEEDED_PREMIUM,

NRP,

UPR_CHARGE,

NEP,

SRCC_TC_COMMISSION,

FOREIGN_CO_COMMISSION,

COMMISSION_INCOME,

NET_REVENUE,

GROSS_CLAIMS,

CLAIMS_RES_FUND,

EXGRATIA_CLIMS_RES_FUND,

CLAIMS_PROCESSING_EXPENSES,

LEGAL_FEES,

RI_RECOVERIES_RI_FUND,

RI_RECOVERIES_SRCC_TC,

RI_RECOVERIES_FOREIGN,

SALVAGE_RECOVERIES,

NET_CLAIMS,

COMMISSION_EXPENSES,

PRODUCTION_BONUS,

DAC_CHARGE,

ACQUISITION_COST,

GROSS_PROFIT,

STAFF_EXPENSES,

ADMIN_EXPENSES,

SELLING_EXPENSES,

FINANCIAL_EXPENSES,

DEPRICIATION_EXPENSES,

MANAGEMENT_EXPENSES,

INV_INCOME_EXT,

INV_INCOME_INT,

OTHER_INCOME_EXT,

OTHER_INCOME_INT,

INV_OTHER_INCOME,

NET_PROFIT

FROM

[..\..\qlickviwe\P&L\P&LDATA2015.QVD]

(qvd);

LOAD BCOD,

NO,

ACCOUNTANT,

AC_NO,

REGION,

BRA_CAT,

BRA_DBASE,

BRANCH,

DEPT

FROM

[..\..\qlickviwe\Branch.qvd]

(qvd);

Directory;

LOAD PRD_CODE,

CLA_CODE,

PRODUCT,

CLASS,

MO_NM,

IBNR_CLASS

FROM

[..\..\qlickviwe\CLASS_PRD.qvd]

(qvd);

SAGARA

Gysbert_Wassenaar · ‎2015-05-05

Let's try that again:

Can you post a source file with data and an excel file with the result you want to achieve, including the calculations?

talk is cheap, supply exceeds demand

sagaraperera · ‎2015-12-22

Dear Mr.Gysbert

I'm trying to get a last year cumulative value in below excel. but i'm can't do it. I want given below table please help me

BCOD	ACTUAL NOV 15	CUMULATIVE UP TO NO 15	ACTUAL NOV 14	CUMULATIVE UP TO NOV 14
CO01	OK	OK	OK	I WANT THIS FIELD
CO02	OK	OK	OK	I WANT THIS FIELD
CO03	OK	OK	OK	I WANT THIS FIELD
CO04	OK	OK	OK	I WANT THIS FIELD

MY EXCELL

YEAR	MONTH	BCOD	Actual
2014	1	A	86,726
2014	2	B	228,627
2014	3	C	482,093
2014	4	D	518,108
2014	5	R	553,123
2014	6	H	775,230
2014	7	J	942,224
2014	8	Y	1,094,839
2014	9	ER	1,112,780
2014	10	D	1,149,908
2014	11	V	1,227,404
2014	12	N	1,326,703
2014	1	K	1,432,231
2014	2	L	1,491,408
2014	3	O	1,505,866
2014	4	UI	1,531,128
2014	5	YH	1,638,844
2014	6	A	1,768,769
2014	7	B	1,878,949
2014	8	C	2,259,313
2014	9	D	2,480,393
2014	10	R	2,810,513
2014	11	H	3,049,719
2014	12	J	3,208,301
2015	1	YH	-17,339
2015	2	A	144,956
2015	3	B	146,225
2015	4	C	238,890
2015	5	D	360,232
2015	6	R	385,811
2015	7	H	459,532
2015	8	J	545,698
2015	9	Y	725,240
2015	10	ER	733,432
2015	1	N	804,744
2015	2	K	900,089
2015	3	L	1,081,749
2015	4	O	1,258,137
2015	5	UI	1,355,269
2015	6	YH	1,413,544
2015	7	A	1,624,346
2015	8	B	1,642,365
2015	19	C	1,767,205
2015	11	D	1,878,944

sagara

Gysbert_Wassenaar · ‎2015-12-23

See these blog posts:

The As-Of Table‌

Accumulations‌

Relative Calendar Fields

talk is cheap, supply exceeds demand