Calculate text based on user selected date range

[#1]

Problem: Based on the date range the user selects, I need to show the "parent" company the "child" company rolls up too. It works if the user clicks in a specific order or clicks on the right names, but we can't proceed like this.

We 2 Parent companies, A and B.

Parent A is always it's own child.

Parent B is always it's own child, UNTIL 1/1/2015. Any date after 1/1/2015, Parent B essentially becomes a child of Parent A. (Parent A company bought company B)

We do this by using a table that has the child, parent, start date they merged, end date they merged.

So we have 3 years of data. 2013, 2014, and 2015.

When we initially open the dashboard, nothing is selected and we assume max data for our data is in 2015 so Company B would be a child of Company A. shown as below in a pivot table

Company A

          Company A

          Company B

If they select only 2014, we'd want to see

Company A

          Company A

Company B

          Company B

If they select 2014 and 2015, we want to see as below. (since max calendar date would fall between that merge date of 1/1/2015 - xxxx)

Company A

     Company A

     Company B

Any help would be greatly appreciated. Our current reporting tool is business objects which we just run a stored proc and reload data every time they user wants to run it, but QlikView is different and I'm not sure how to basically calculate what parent company should display based on what date range the user picks against the date range for companies merging.

If it's needed, If the company has never merged, they have no record in our "merger table". Companies only have records in that table if they have been merged, other wise it's assumed parent company = child company.

THANK YOU

[#2]

where you are trying to display this Company name?

[Oleg_Troyansky]

Hi James,

Unfortunately, it would be impossible to facilitate dynamic association of this kind in the data set, - but you already know that. With that understanding in mind, we can talk about various ways of facilitating this logic in various sheet objects. The question here is where would you like to "see it" this way?

- If it's a list box, then you have to use a conditional "Expression" and not a field. In the expression, you can calculate the relevant "child" (or "parent") based on the possible (selected) dates.

- If it's a chart, then you could use Set Analysis to facilitate the dynamic filtering, or as a worst case, use another conditional calculation, which might be heavy on a large data set.

- In case you wanted to show this structure in a chart dimension, you should create a Calculated Dimension that facilitates the same conditional logic.

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

[#4]

First field in the Pivot table. Layout is shown below. I can't post qvw's due to sensitivity around the data.

Parent Company

          All child companies of parent company

Next Parent Company

           all child companies

Need to calculate the parent company being used based on time period selected

[Oleg_Troyansky]

James,

you could create a "straw-man" model with made up data, that demonstrate your problem.

I can offer an example of such a calculation, but it can only be a theoretical example:

Let's say for example that your Pivot Table has 3 Dimensions: Parent, Child, and Product. The Parent needs to be calculated based on the available date. The Calculated Dimension for the Parent could look like this:

AGGR(

     FirstSortedValue(Parent, MergerDate),

     Child, Product)

Assuming the Parents are defined in a table by Child and MergerDate, the earliest available value of the Parent will be picked for each distinct combination of Child and Product. If you need any other dimensions in your Pivot Table, add them to the AGGR() function as dimensions.

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

[Digvijay_Singh]

See if this attached sample helps -