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 (sample).
Output Table required
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.
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.
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))
Check the sample attached here -
Re: QoQ Growth calculation using multiple dimensions in StraightTable