topic QoQ Growth calculation using multiple dimensions in StraightTable in QlikView

QoQ Growth calculation using multiple dimensions in StraightTable

Anonymous — Wed, 25 Nov 2020 16:16:04 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

Re: QoQ Growth calculation using multiple dimension in StraingtTable

Digvijay_Singh — Fri, 12 May 2017 21:29:47 GMT

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

Re: QoQ Growth calculation using multiple dimension in StraingtTable

Anonymous — Mon, 15 May 2017 06:50:33 GMT

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.

Re: QoQ Growth calculation using multiple dimensions in StraightTable

Digvijay_Singh — Wed, 17 May 2017 01:16:49 GMT

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;

Re: QoQ Growth calculation using multiple dimensions in StraightTable

Digvijay_Singh — Wed, 17 May 2017 01:17:58 GMT

Check the QVW attached.

Re: QoQ Growth calculation using multiple dimensions in StraightTable

Anonymous — Wed, 17 May 2017 07:24:41 GMT

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.

Re: QoQ Growth calculation using multiple dimensions in StraightTable

Digvijay_Singh — Wed, 17 May 2017 15:08:20 GMT

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;

Re: QoQ Growth calculation using multiple dimensions in StraightTable

Digvijay_Singh — Wed, 17 May 2017 15:09:43 GMT

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

Re: QoQ Growth calculation using multiple dimensions in StraightTable

Anonymous — Thu, 18 May 2017 10:33:06 GMT

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 ?

Re: QoQ Growth calculation using multiple dimensions in StraightTable

Digvijay_Singh — Thu, 18 May 2017 15:37:07 GMT

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;

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

Re: QoQ Growth calculation using multiple dimensions in StraightTable

Anonymous — Fri, 19 May 2017 14:37:33 GMT

Thank you very much. The solution is as expected.

If you dont mind, I have another query on the same set of data.

I have filters as above, My selections are

From_Report_year : 1995

From_Report_Qtr : 91995

To_Report_year : 1996

To_Report_Qtr : 91996

I want to Calculate Growth difference of only the matching D_MONTH values under the selected quarters. Here it is

650 (81995) - 150 (81995) = 500.

How I can implement this ? Is that I need to frame two master calendars on data ?

Can you please help me. Your thoughts and inputs are very much appreciated and helpful.

digvijay‌

Re: QoQ Growth calculation using multiple dimensions in StraightTable

Digvijay_Singh — Tue, 23 May 2017 03:51:47 GMT

You can use alternate state to achieve this, see attached, you may need some changes to meet your specific requirements -

Re: QoQ Growth calculation using multiple dimensions in StraightTable

Anonymous — Tue, 23 May 2017 04:10:43 GMT

Thanks. I will check. but in addition to matching rows, I also need to show un-matching rows(where the difference would be null).

So I think this might be achieved only at back end script.

For sample date selections, this should be the result table.

Re: QoQ Growth calculation using multiple dimensions in StraightTable

Digvijay_Singh — Tue, 23 May 2017 04:42:46 GMT

Check this -

Re: QoQ Growth calculation using multiple dimensions in StraightTable

Anonymous — Tue, 23 May 2017 05:14:31 GMT

'Difference' result calculations are coming correctly,

but I need to achieve the exact format of Result table(with columns D_YEAR,D_MONTH,AMOUNT,Difference).

Can this be achieved at backend script ? in any possible way ?

I tried assigning flags in script, but that didn't work for me.

Re: QoQ Growth calculation using multiple dimensions in StraightTable

Anonymous — Tue, 23 May 2017 16:16:09 GMT

Any possible way to achieve exact format of Result table(with columns D_YEAR,D_MONTH,AMOUNT,Difference). ?

digvijay‌

using back end script ? Can you please help.

Re: QoQ Growth calculation using multiple dimensions in StraightTable

Digvijay_Singh — Tue, 23 May 2017 16:27:52 GMT

I think you don't need further work in script as required fields are already available to process what you need, let me try today sometime, was thinking of using addyear(Date,-1) to calculate the amount of last year for difference calculation.

Re: QoQ Growth calculation using multiple dimensions in StraightTable

Anonymous — Tue, 23 May 2017 17:03:05 GMT

ok. In sample data I have given two years for difference calculation. I have historical data (multiple years) where there is chance of comparing between 30-40 yrs.
since historical data is huge, I think front end logic will give performance issue, and looking for back end script logic.

Re: QoQ Growth calculation using multiple dimensions in StraightTable

Digvijay_Singh — Tue, 23 May 2017 17:05:33 GMT

Ok, make sense, will do something today on this.

Re: QoQ Growth calculation using multiple dimensions in StraightTable

Digvijay_Singh — Tue, 23 May 2017 19:42:49 GMT

I could manage to calculate difference in script, check below -