QoQ Growth calculation using multiple dimensions in StraightTable

Anonymous — Tue, 16 May 2017 09:28:22 GMT

I have sample data associated as below.

DIV	FLAG	PARENT_GRP	CHILD_GRP	O_LOB	F_LOB	REPORT_YEAR	REPORT_QUARTER	D_YEAR	D_MONTH	AMOUNT
DIMENSIONS										EXPRESSION
10	A	P1	C1	LOB1	FLOB1	1995	091995	1995	071995	100
10	A	P1	C1	LOB1	FLOB1	1995	091995	1995	081995	150
10	A	P1	C1	LOB1	FLOB1	1995	121995	1995	071995	200
10	A	P1	C1	LOB1	FLOB1	1995	121995	1995	081995	100
10	A	P1	C1	LOB1	FLOB1	1996	031996	1995	071995	300
10	A	P1	C1	LOB1	FLOB1	1996	031996	1995	081995	350
10	A	P1	C1	LOB1	FLOB1	1996	061996	1995	081995	400
10	A	P1	C1	LOB1	FLOB1	1996	061996	1995	091995	450
10	A	P1	C1	LOB1	FLOB1	1996	061996	1995	111995	500
10	A	P1	C1	LOB1	FLOB1	1996	091996	1995	011995	550
10	A	P1	C1	LOB1	FLOB1	1996	091996	1995	061995	600
10	A	P1	C1	LOB1	FLOB1	1996	091996	1995	081995	650
10	A	P1	C1	LOB1	FLOB1	1996	091996	1995	091995	700
10	A	P1	C1	LOB1	FLOB1	1996	091996	1995	101995	750
10	A	P1	C1	LOB1	FLOB1	1996	091996	1995	111995	800
10	A	P1	C1	LOB1	FLOB1	1996	121996	1995	081995	500

10	B	P1	C2	LOB1	FLOB1	1995	031995	1995	011995	100
10	B	P1	C2	LOB1	FLOB1	1995	061995	1995	011995	200
10	B	P1	C2	LOB1	FLOB1	1996	091996	1995	011995	200
20	A	P2	C1	LOB1	FLOB1	1996	121996	1995	081995	100
20	A	P2	C1	LOB2	FLOB2	1996	091996	1995	081995	300
20	B	P2	C1	LOB3	FLOB3	1996	121996	1995	091995	400

I want to calculate Quarterly growth AMOUNT for each quarter of 'REPORT_QUARTER' based on maximum of 'D_MONTH' for all years of 'REPORT_YEAR' based on dimensions DIV, FLAG, P_GRP, C_GRP, O_LOB, F_LOB, REPORT_YEAR, REPORT_QUARTER.

I want to display the result data in straight table as below for DIV=10, FLAG=A, P_GRP=P1, C_GRP=C1, O_LOB=LOB1, F_LOB=FLOB1 for all years. With below REPORT_YEAR, QUARTER multibox filter.

REPORT_ YEAR	REPORT_ QUARTER	DIV	FLAG	P_GRP	C_GRP	O_LOB	F_LOB	D_YEAR	D_MONTH	AMOUNT	Quarterly GROWTH	Calculation for understanding
Multibox Filter (For understdng, not to display in table)		Dimensions						Expression	Expression
1995	091995	10	A	P1	C1	LOB1	FLOB1	1995	071995	100	-
1995	091995	10	A	P1	C1	LOB1	FLOB1	1995	081995	150	-
1995	121995	10	A	P1	C1	LOB1	FLOB1	1995	071995	200	-
1995	121995	10	A	P1	C1	LOB1	FLOB1	1995	081995	100	-50	(100-150)
1996	031996	10	A	P1	C1	LOB1	FLOB1	1995	071995	300	-
1996	031996	10	A	P1	C1	LOB1	FLOB1	1995	081995	350	250	(350-100)
1996	061996	10	A	P1	C1	LOB1	FLOB1	1995	081995	400	-
1996	061996	10	A	P1	C1	LOB1	FLOB1	1995	091995	450	-
1996	061996	10	A	P1	C1	LOB1	FLOB1	1995	111995	500	150	(500-350)
1996	091996	10	A	P1	C1	LOB1	FLOB1	1995	011995	550	-
1996	091996	10	A	P1	C1	LOB1	FLOB1	1995	061995	600	-
1996	091996	10	A	P1	C1	LOB1	FLOB1	1995	081995	650	-
1996	091996	10	A	P1	C1	LOB1	FLOB1	1995	091995	700	-
1996	091996	10	A	P1	C1	LOB1	FLOB1	1995	101995	750	-
1996	091996	10	A	P1	C1	LOB1	FLOB1	1995	111995	800	300	(800-500)
1996	121996	10	A	P1	C1	LOB1	FLOB1	1995	081995	500	-300	(500-800)

If user select year '1995', user should be able to see Quarterly GROWTH of -50 which is based on latest D_MONTH '081995' of REPORT_QUARTER's 091995 & 121995, likewise for other years.

Can anybody please help. Thanks in Advance. Thanks for your time.

Note: I cannot paste screenshot or developed application due to environment restrictions and confidentiality of data.

You can take the above table data as below.

Load *,MakeDate(Right(D_MONTH,4),left(D_MONTH,2)) as DATE;

LOAD DIV,

FLAG,

PARENT_GRP,

CHILD_GRP,

O_LOB,

F_LOB,

REPORT_YEAR,

REPORT_QUARTER,

D_YEAR,

D_MONTH,

AMOUNT

FROM

[https://community.qlik.com/thread/260354]

(html, codepage is 1252, embedded labels, table is @1, filters(

Remove(Row, Pos(Top, 19)),

Remove(Row, Pos(Top, 1))

Thanks,

Sri

topic QoQ Growth calculation using multiple dimensions in StraightTable in QlikView

QoQ Growth calculation using multiple dimensions in StraightTable