topic Re: GROWTH RATE in QlikView

GROWTH RATE

sagaraperera — Thu, 02 Jul 2015 04:58:33 GMT

Dear all

I want to calculate a growth rate for sale. my script have a 2014 and 2015 sale details of cluster wise. i want calculate given below formula

MY qvd attached.

2015 GROSSTOT-2014 GROSSTOT/ 2014 GROSSTOT = GROWTH RATE

MY SCRIPT

LOAD BCOD,

CLA_CODE,

PRD_CODE ,

POLICY_NO,

REF_NO,

MARKETING_DATE,

MDATE,

MONTH(MARKETING_DATE) AS MMONTH,

YEAR,

MONTH,

NAME,

ME_CODE,

TRN_DATE,

TRAN_YEAR,

TRAN_MONTH,

POL_PERIOD_FROM,

SCCOD,

CP,

RS,

TC,

GROSSTOT,

TYPE,

N_R_NOS1,

F_NOS2

FROM

G:\qlickviwe\Sales\SALES2015.QVD

(qvd);

DIRECTORY;

LOAD BCOD,

CLA_CODE,

PRD_CODE ,

POLICY_NO,

REF_NO,

MARKETING_DATE,

MDATE,

MONTH(MARKETING_DATE) AS MMONTH,

YEAR,

MONTH,

NAME,

ME_CODE,

TRN_DATE,

TRAN_YEAR,

TRAN_MONTH,

POL_PERIOD_FROM,

SCCOD,

CP,

RS,

TC,

GROSSTOT,

TYPE,

N_R_NOS1,

F_NOS2

FROM

G:\qlickviwe\Sales\SALES2014.QVD

(qvd);

Re: GROWTH RATE

arulsettu — Thu, 02 Jul 2015 05:02:48 GMT

try this

(sum({<YEAR={$(=max(YEAR))}>}GROSSTOT)-sum({<YEAR={$(=max(YEAR)-1)}>}GROSSTOT))/sum({<YEAR={$(=max(YEAR))}>}GROSSTOT)

Re: GROWTH RATE

gautik92 — Thu, 02 Jul 2015 05:08:09 GMT

Try this

(sum({<YEAR={$(=max(YEAR))},MonthID={$(=Max(MonthID))}>}GROSSTOT)-sum({<YEAR={$(=max(YEAR)-1),MonthID={$(=Max(MonthID))}>}GROSSTOT))/sum({<YEAR={$(=max(YEAR))}>}GROSSTOT)

Re: GROWTH RATE

sagaraperera — Thu, 02 Jul 2015 05:24:27 GMT

THANKS DEAR IT IS CORRECT

SAGARA

Re: GROWTH RATE

arulsettu — Thu, 02 Jul 2015 05:26:03 GMT

please mark it as correct if it is solve your problem

Re: GROWTH RATE

sagaraperera — Thu, 02 Jul 2015 05:33:56 GMT

how to mark as corect

Re: GROWTH RATE

arulsettu — Thu, 02 Jul 2015 05:38:14 GMT

below my answer there is a correct answer button with a green star clik it

Re: GROWTH RATE

jagan — Thu, 02 Jul 2015 06:05:28 GMT

Hi,

Try this expression, you need to include TRN_DATE=, TRAN_MONTH= in your expression otherwise whenever you select month or date it will changes to the Month or date selected.

(sum({<TRN_DATE=, TRAN_MONTH=,

TRAN_YEAR={$(=max(TRAN_YEAR))}>}GROSSTOT)-sum({<TRN_DATE=, TRAN_MONTH=, TRAN_YEAR={$(=max(TRAN_YEAR)-1)}>}GROSSTOT))/sum({<TRN_DATE=, TRAN_MONTH=, TRAN_YEAR={$(=max(TRAN_YEAR))}>}GROSSTOT)