Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Dynamic growth calculation based on filter selection in straight table

I have sample data associated as below.

DIMENSIONSEXPRESSION

DIV

REPORT_YEAR

REPORT_QUARTERD_MONTHAMOUNT
101995091995071995100
101995091995081995150
101995121995071995200
101995121995081995100
101996031996071995300
101996031996081995350
101996061996081995400
101996061996091995450
101996061996111995500
101996091996011995550
101996091996061995600
101996091996081995650
101996091996091995700
101996091996101995750
101996091996111995800
101996121996081995500

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

8 Replies
Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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_YEARREPORT_QUARTER         DIVD_MONTHAMOUNT

Growth/

Difference

1995919951071995100-
1995919951081995150-
1996919961011995550-
1996919961061995600-
1996919961081995650500  (650-150)
1996919961091995700-
19969199610101995750-
19969199610111995800-

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.

Anonymous
Not applicable
Author

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

Couple of scenarios in this one.

Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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 ?

Anonymous
Not applicable
Author

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.