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 |
Requirement:
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 each of the selected Report_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 ? or a master calendar with two dates ? or any other approach ?
Can you please help me. Your thoughts and inputs are very much appreciated and helpful.
Thanks,
Sri
rwunderlich; jagandvqlikviewstalwar1swuehljohnwmrkachhiaimp;tamil.n@live.com; marcus_sommertamilarasuatkinsowatkinsowtamilarasumarcus_sommer
Note: I cannot upload data in excel 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_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, 1)) )) ;
Thanks,
Sri
There is probably a much more elegant way but this works for me.
Thank you very much. Your solution works perfect. Now I am figuring out a way to display the growth/difference per filter selections in result table with all rows under each quarter (Not just alone the matching D_MONTH rows) as below.
There is need to add few more dimensions(Like DIV) and the difference should come as below considering addtl dimensions.
Date Filters. not to be displayed in table | ||||||
REPORT_YEAR | REPORT_QUARTER | DIV | D_MONTH | AMOUNT | Growth/ Difference | |
---|---|---|---|---|---|---|
1995 | 91995 | 10 | 71995 | 100 | - | |
1995 | 91995 | 10 | 81995 | 150 | - | |
1996 | 91996 | 10 | 11995 | 550 | - | |
1996 | 91996 | 10 | 61995 | 600 | - | |
1996 | 91996 | 10 | 81995 | 650 | 500 (650-150) | |
1996 | 91996 | 10 | 91995 | 700 | - | |
1996 | 91996 | 10 | 101995 | 750 | - | |
1996 | 91996 | 10 | 111995 | 800 | - |
This is where I am thinking if it should be implemented using master calendar with 2 dates, or any other approach. Any one have any ideas on how it can be implemented ? This is bit urgent.
Thanks in Advance.
Not sure what you're needing. Maybe something like this?
Couple of scenarios in this one.
This is something close.
If I do selections from main table like below, I am only getting single row for 081995 and 0's for 091995 & 111995 under From column.
but expected result should have all values for all rows. and 2 rows for 81995
Can the above result table be achieved at backend script in any other approach ? I tried assigning flags at backend script, but that didnt work for me. atkinsow
Your help is very much appreciated.
Getting a little confused. But see if this is more your liking. Table V4
Thank you. Can this be achieved at backend script ? As the actual data is available for approx 40 years.
there is chance of comparing for example: 1980 yr with 2000 yr financials. Front end logic may not work ?
I think it'll work as-is. See attached. If I select 2 years in the listbox it compares just the 2 that are selected.
It always compares to the prior available year.