Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have sample data associated as below.
DIMENSIONS | EXPRESSION | |||
DIV | REPORT_YEAR | REPORT_QUARTER | D_MONTH | AMOUNT |
---|---|---|---|---|
10 | 1995 | 091995 | 071995 | 100 |
10 | 1995 | 091995 | 081995 | 150 |
10 | 1995 | 121995 | 071995 | 200 |
10 | 1995 | 121995 | 081995 | 100 |
10 | 1996 | 031996 | 071995 | 300 |
10 | 1996 | 031996 | 081995 | 350 |
10 | 1996 | 061996 | 081995 | 400 |
10 | 1996 | 061996 | 091995 | 450 |
10 | 1996 | 061996 | 111995 | 500 |
10 | 1996 | 091996 | 011995 | 550 |
10 | 1996 | 091996 | 061995 | 600 |
10 | 1996 | 091996 | 081995 | 650 |
10 | 1996 | 091996 | 091995 | 700 |
10 | 1996 | 091996 | 101995 | 750 |
10 | 1996 | 091996 | 111995 | 800 |
10 | 1996 | 121996 | 081995 | 500 |
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, REPORT_YEAR, REPORT_QUARTER.
I want to display the result data in straight table as below. With below REPORT_YEAR, QUARTER multibox filter and DIV listbox filter.
Multibox Filter (For understdng, not to display in table) | Dimensions | Expression | ||||||
REPORT_ YEAR | REPORT_ QUARTER | DIV | D_MONTH | AMOUNT | Quarterly GROWTH | Calculation for understanding | ||
---|---|---|---|---|---|---|---|---|
1995 | 091995 | 10 | 071995 | 100 | - | |||
1995 | 091995 | 10 | 081995 | 150 | - | |||
1995 | 121995 | 10 | 071995 | 200 | - | |||
1995 | 121995 | 10 | 081995 | 100 | -50 | (100-150) | ||
1996 | 031996 | 10 | 071995 | 300 | - | |||
1996 | 031996 | 10 | 081995 | 350 | 250 | (350-100) | ||
1996 | 061996 | 10 | 081995 | 400 | - | |||
1996 | 061996 | 10 | 091995 | 450 | - | |||
1996 | 061996 | 10 | 111995 | 500 | 150 | (500-350) | ||
1996 | 091996 | 10 | 011995 | 550 | - | |||
1996 | 091996 | 10 | 061995 | 600 | - | |||
1996 | 091996 | 10 | 081995 | 650 | - | |||
1996 | 091996 | 10 | 091995 | 700 | - | |||
1996 | 091996 | 10 | 101995 | 750 | - | |||
1996 | 091996 | 10 | 111995 | 800 | 300 | (800-500) | ||
1996 | 121996 | 10 | 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 without year end breakup. DIV values will change.
Can anybody please help. Thanks in Advance. Thanks for your time.
rwunderlich; deepakk; dvadithala; dvqlikview; mrkachhiaimp; swuehl
Note: I cannot paste screenshot or developed application due to environment restrictions and confidentiality of data.
You can take the above table data as.
Load *,MakeDate(Right(D_MONTH,4),left(D_MONTH,2)) as DATE;
LOAD DIV,
REPORT_YEAR,
REPORT_QUARTER,
D_MONTH,
AMOUNT
FROM
// [https://community.qlik.com/thread/260354]
(html, codepage is 1252, embedded labels, table is @1, filters(
Remove(Row, Pos(Top, 1))
Please do not post your question twice in Qlik Community. One time will do, and we can all see your post in the General Overview anyway. Otherwise you will split the attention of the Community and as a result you may not get the focus & answers you need.
Quarterly growth in straight table
You can still remove one of these identical threads by using the Delete function in the Actions menu and at the top right of the discssion header.
Thank you.
Check the sample attached here -
Re: QoQ Growth calculation using multiple dimensions in StraightTable