Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

sreekanth_qlik
New Contributor III

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

25 Replies
Digvijay_Singh
Honored Contributor III

Re: QoQ Growth calculation using multiple dimension in StraingtTable

I could almost show what you want through front end solution except one value 350, don't know yet why it is showing wrong .

Capture.PNG

sreekanth_qlik
New Contributor III

Re: QoQ Growth calculation using multiple dimension in StraingtTable

Hi Dijgvijay,

Thanks for your reply. Yes, value 350 is wrong. These values are being calculated on single year. I have to display the quarter growth comparing multiple years. Also I am looking for back end logic as the data size is in millions and using front end logic results in performance issues.

Apologies If I haven't provided my requirement clearly. I have edited my question.

My Approach:  I am implementing below back end logic considering multiple dimensions. I have given list box filters for DIV, FLAG, P_GRP, C_GRP, O_LOB, F_LOB and multibox for REPORT_YEAR and REPORT_QUARTER.

I am able to get the Quarterly GROWTH for 1995 alone correctly with below approach, by framing 2 Keys ( Key with Report date and KeyWithoutReportingDate without Report date ) assigning a Flag'1' for each Min and Max report date.

TABLE1:

LOAD

*,

AutoNumber(DIV &'_'& FLAG &'_'& PARENT_GROUP &'_'& ReportingDate & '_' & CHILD_GROUP & '_' & F_LOB & '_' & O_LOB) AS Key,

AutoNumber(DIV &'_'& FLAG &'_'& PARENT_GROUP &'_'& CHILD_GROUP & '_' & F_LOB & '_' & O_LOB) AS KeyWithoutReportingDate;

LOAD

     DIV,

     FLAG,

     PARENT_GROUP, 

     REPORT_YEAR,

     REPORT_MONTH,

     MakeDate(REPORT_YEAR, left(REPORT_MONTH,2)) AS ReportingDate,

     CHILD_GROUP,

     F_LOB,

     O_LOB,

     D_YEAR,

     D_MONTH,

     MakeDate(D_YEAR, left(D_MONTH,2)) AS DDate,

     AMOUNT

FROM  file;

LEFT JOIN(TABLE1)

LOAD

Key,

Date(Max(DDate)) AS DDate,

1 AS MaxDDate

RESIDENT TABLE1

Group By Key;

LEFT JOIN(TABLE1)

LOAD

KeyWithoutReportingDate,

Date(Max(ReportingDate)) AS ReportingDate,

1 AS MaxReportDate

RESIDENT Pivot_Temp

Group By KeyWithoutReportingDate;

LEFT JOIN(TABLE1)

LOAD

KeyWithoutReportingDate,

Date(Min(ReportingDate)) AS ReportingDate,

1 AS MinReportDate

RESIDENT TABLE1

Group By KeyWithoutReportingDate;

LEFT JOIN(TABLE1)

LOAD

KeyWithoutReportingDate,

1 AS MaxDDate,

1 AS MaxReportDate,

AMOUNT AS AMOUNT_Prior

RESIDENT TABLE1

WHERE MaxDDate = 1 AND MinReportDate = 1;

///--------------------

Data:

LOAD RowNo() AS RowNo,

     KeyWithoutReportingDate,

     MaxDDate,

     MaxReportDate,

     ReportingDate,

DDate,

     Key,

DIV,

    FLAG,

     PARENT_GROUP,

CHILD_GROUP,

F_LOB,

O_LOB,

MinReportDate,

AMOUNT_Prior

taking 'AMOUNT - AMOUNT_Prior' expression logic as column 'Quarterly GROWTH' in table.

, but I am unable to see 'Quarterly GROWTH' if there are 4 quarters for year 1996 (Obviously bcoz I'm only extracting min and max rep dates).

I tried other ways of getting 'Growth' for all the years having more than 2 quarters data each, but in vain.

Is there any other way I can calculate GROWTH without framing Keys based on dimensions.

Digvijay_Singh
Honored Contributor III

Re: QoQ Growth calculation using multiple dimensions in StraightTable

May be something like this -

Data:

LOAD DIV,

     FLAG,

     PARENT_GRP,

     CHILD_GRP,

     O_LOB,

     F_LOB,

     REPORT_YEAR,

     REPORT_QUARTER,

     AutoNumber(REPORT_QUARTER) as QuarterSeqNo,

     D_YEAR,

     D_MONTH,

     MakeDate(Right(D_MONTH,4),left(D_MONTH,2)) as Month_Date,

     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))

))

;

MaxQuarter:

Load

  DIV,

     FLAG,

     PARENT_GRP,

     CHILD_GRP,

     O_LOB,

     F_LOB,

  REPORT_YEAR,

     REPORT_QUARTER,

     QuarterSeqNo,

     D_YEAR,

     FirstSortedValue(AMOUNT,-D_MONTH) as MaxQAmount

Resident Data

Group By

  DIV,

     FLAG,

     PARENT_GRP,

     CHILD_GRP,

     O_LOB,

     F_LOB,

  REPORT_YEAR,

     REPORT_QUARTER,

     QuarterSeqNo,

     D_YEAR

Order By DIV,

     FLAG,

     PARENT_GRP,

     CHILD_GRP,

     O_LOB,

     F_LOB,

  REPORT_YEAR,

     REPORT_QUARTER,

     QuarterSeqNo,

     D_YEAR

;

QuarterlyGrowth:

Load *,

  if(RecNo()=1,0,Rangesum(MaxQAmount,-Peek(MaxQAmount))) as QGrowthAmount

Resident MaxQuarter;

Drop table MaxQuarter;

Drop table Data;

Capture.PNG

Digvijay_Singh
Honored Contributor III

Re: QoQ Growth calculation using multiple dimensions in StraightTable

Check the QVW attached.

sreekanth_qlik
New Contributor III

Re: QoQ Growth calculation using multiple dimensions in StraightTable

Thank you very much. This gives only the latest month rows with 'QGrowthAmount' correctly for each Report_Quarter of Report_year. However I also have to display all the rows under each report_year (This is where I am lagging)

For example if I select Rep_year - 1995, It should display as below.

If I select Rep_Year 1995, 1996. QtrGrowth should come continuously without yearly breakup.

Digvijay_Singh
Honored Contributor III

Re: QoQ Growth calculation using multiple dimensions in StraightTable

I think that shouldn't be problem, we can join the final table with the first table. Here is the updated script -

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

Data:

LOAD DIV,

    FLAG,

    PARENT_GRP,

    CHILD_GRP,

    O_LOB,

    F_LOB,

    REPORT_YEAR,

    REPORT_QUARTER,

    AutoNumber(REPORT_QUARTER) as QuarterSeqNo,

    D_YEAR,

    D_MONTH,

    MakeDate(Right(D_MONTH,4),left(D_MONTH,2)) as Month_Date,

    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))

))

;

MaxQuarter:

Load

  DIV,

    FLAG,

    PARENT_GRP,

    CHILD_GRP,

    O_LOB,

    F_LOB,

  REPORT_YEAR,

    REPORT_QUARTER,

    QuarterSeqNo,

    D_YEAR,

    FirstSortedValue(D_MONTH,-Month_Date) as D_MONTH,

    FirstSortedValue(AMOUNT,-Month_Date) as MaxQAmount

Resident Data

Group By

  DIV,

    FLAG,

    PARENT_GRP,

    CHILD_GRP,

    O_LOB,

    F_LOB,

  REPORT_YEAR,

    REPORT_QUARTER,

    QuarterSeqNo,

    D_YEAR

Order By DIV,

    FLAG,

    PARENT_GRP,

    CHILD_GRP,

    O_LOB,

    F_LOB,

  REPORT_YEAR,

    REPORT_QUARTER,

    QuarterSeqNo,

    D_YEAR

;

QuarterlyGrowth:

Load *,

  if(RecNo()=1,0,Rangesum(MaxQAmount,-Peek(MaxQAmount))) as QGrowthAmount

Resident MaxQuarter;

join(QuarterlyGrowth)

Load *

Resident Data;

Drop table MaxQuarter;

Drop table Data;

Capture.PNG

Digvijay_Singh
Honored Contributor III

Re: QoQ Growth calculation using multiple dimensions in StraightTable

Since we are doing quarter wise calculation, it won't stop at year end, will keep on calculating irrespective of year end.

sreekanth_qlik
New Contributor III

Re: QoQ Growth calculation using multiple dimensions in StraightTable

Thanks again.

The new code changes are working perfectly as expected for Div=10.

However the QGrowthAmount should be displayed 0 for Div=20, Flag=A, P2,C1,LOB1,FLOB1 filter selections as there are no previous quarter for the same selection. Likewise for other two rows under Div=20.

I guess we might have to change the way we handle these many dimensions in script. Any other approach ?

Digvijay_Singh
Honored Contributor III

Re: QoQ Growth calculation using multiple dimensions in StraightTable

This is not big deal, our script was considering all set of record continuous with no segments based on change of dimension, you can change below condition or used exactly same to assign 0 when new dimension value is available as we process our records in sequence -

QuarterlyGrowth:

Load *,

if(DIV=Previous(DIV) and FLAG=Previous(FLAG) and PARENT_GRP = Previous(PARENT_GRP)

  and CHILD_GRP = Previous(CHILD_GRP) and O_LOB=Previous(O_LOB) and F_LOB=Previous(F_LOB)

  ,Rangesum(MaxQAmount,-Peek(MaxQAmount)),0) as QGrowthAmount

Resident MaxQuarter;

Capture.PNG

Please mark helpful/correct the responses if you feel you could get some help out of my responses.