topic Re: Dynamic growth calculation based on filter selection in straight table in QlikView

Dynamic growth calculation based on filter selection in straight table

Anonymous — Fri, 19 May 2017 16:31:22 GMT

I have sample data associated as below.

DIV	REPORT_YEAR	REPORT_QUARTER	D_MONTH	AMOUNT
DIMENSIONS				EXPRESSION
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‌; jagan‌dvqlikview‌stalwar1‌swuehl‌johnw‌mrkachhiaimp‌;tamil.n@live.com; marcus_sommer tamilarasu atkinsow atkinsow tamilarasu marcus_sommer ‌

Re: Dynamic growth calculation based on filter selection in straight table

Anonymous — Fri, 19 May 2017 16:42:37 GMT

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

Re: Dynamic growth calculation based on filter selection in straight table

Anonymous — Fri, 19 May 2017 21:13:39 GMT

There is probably a much more elegant way but this works for me.

Re: Dynamic growth calculation based on filter selection in straight table

Anonymous — Sat, 20 May 2017 06:17:47 GMT

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.

REPORT_YEAR	REPORT_QUARTER	DIV	D_MONTH	AMOUNT	Growth/ Difference
Date Filters. not to be displayed in table
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.

Re: Dynamic growth calculation based on filter selection in straight table

Anonymous — Mon, 22 May 2017 14:24:04 GMT

Not sure what you're needing. Maybe something like this?

Couple of scenarios in this one.

Re: Dynamic growth calculation based on filter selection in straight table

Anonymous — Mon, 22 May 2017 16:43:47 GMT

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.

Re: Dynamic growth calculation based on filter selection in straight table

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

Getting a little confused. But see if this is more your liking. Table V4

Re: Dynamic growth calculation based on filter selection in straight table

Anonymous — Wed, 24 May 2017 04:16:44 GMT

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 ?

Re: Dynamic growth calculation based on filter selection in straight table

Anonymous — Wed, 24 May 2017 18:17:04 GMT

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.