0 Replies Latest reply: May 16, 2017 5:28 AM by Sri B RSS

    QoQ Growth calculation using multiple dimensions in StraightTable

    Sri B

      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