Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
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

25 Replies
Anonymous
Not applicable
Author

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

Digvijay_Singh

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

Capture.PNG

Anonymous
Not applicable
Author

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.

Digvijay_Singh

Check this -

Capture.PNG

Anonymous
Not applicable
Author

'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.

Anonymous
Not applicable
Author

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.

Digvijay_Singh

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.

Anonymous
Not applicable
Author

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.

Digvijay_Singh

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

Digvijay_Singh

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

Capture.PNG