Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

QoQ Growth calculation using multiple dimensions in StraightTable

I have sample data associated as below.

DIMENSIONSEXPRESSION

DIV

FLAG

PARENT_GRP

CHILD_GRP

O_LOB

F_LOB

REPORT_YEAR

REPORT_QUARTER

D_YEAR

D_MONTHAMOUNT
10AP1C1LOB1FLOB119950919951995071995100
10AP1C1LOB1FLOB119950919951995081995150
10AP1C1LOB1FLOB119951219951995071995200
10AP1C1LOB1FLOB119951219951995081995100
10AP1C1LOB1FLOB119960319961995071995300
10AP1C1LOB1FLOB119960319961995081995350
10AP1C1LOB1FLOB119960619961995081995400
10AP1C1LOB1FLOB119960619961995091995450
10AP1C1LOB1FLOB119960619961995111995500
10AP1C1LOB1FLOB119960919961995011995550
10AP1C1LOB1FLOB119960919961995061995600
10AP1C1LOB1FLOB119960919961995081995650
10AP1C1LOB1FLOB119960919961995091995700
10AP1C1LOB1FLOB119960919961995101995750
10AP1C1LOB1FLOB119960919961995111995800
10AP1C1LOB1FLOB119961219961995081995500
10BP1C2LOB1FLOB119950319951995011995100
10BP1C2LOB1FLOB119950619951995011995200
10BP1C2LOB1FLOB119960919961995011995200
20AP2C1LOB1FLOB119961219961995081995100
20AP2C1LOB2FLOB219960919961995081995300
20BP2C1LOB3FLOB319961219961995091995400

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.

Multibox Filter
(For understdng, not to display in table)
DimensionsExpressionExpression

REPORT_

YEAR

REPORT_

QUARTER

DIVFLAGP_GRPC_GRPO_LOBF_LOBD_YEARD_MONTHAMOUNTQuarterly GROWTHCalculation for understanding
199509199510AP1C1LOB1FLOB11995071995100     -
199509199510AP1C1LOB1FLOB11995081995150     -
199512199510AP1C1LOB1FLOB11995071995200     -
199512199510AP1C1LOB1FLOB11995081995100-50(100-150)
199603199610AP1C1LOB1FLOB11995071995300     -
199603199610AP1C1LOB1FLOB11995081995350250(350-100)
199606199610AP1C1LOB1FLOB11995081995400      -
199606199610AP1C1LOB1FLOB11995091995450      -
199606199610AP1C1LOB1FLOB11995111995500150(500-350)
199609199610AP1C1LOB1FLOB11995011995550      -
199609199610AP1C1LOB1FLOB11995061995600      -
199609199610AP1C1LOB1FLOB11995081995650      -
199609199610AP1C1LOB1FLOB11995091995700      -
199609199610AP1C1LOB1FLOB11995101995750      -
199609199610AP1C1LOB1FLOB11995111995800300(800-500)
199612199610AP1C1LOB1FLOB11995081995500-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

0 Replies