Mapping Fields - Aggr. Group vs. Single

[#1]

Hi,

usually I compare a group of companies against another single company within a sector.

In the attached example I do compare A and B as the group against H.

On this account I have created a chart with the corresponding company dimensions in the set analysis.

What I wanna do now is to examine the data within a sector on corporate group level with the same target of

comparing groups. Hence, I have aggregated the data in the load script.

My problem now is that in my original qlikview document many analysis including the set analysis on company level are implemented.

Is there any way to map or use an if clause to switch between the two levels without changing all expressions in the chart.

Like when choosing the corporate group level, this substitutes the company field.

So for the attached example, leave the chart unchanged. Now I would like the aggregated values of group 1 and as peer group 2 compared in the chart.

I hope I made myself clear.

Many thanks in advance!

[kaushiknsolanki]

Hi,

Sorry but I am not clear with your problem.

Can you give example of the data which you shared. What selection you are doing and what output you are expecting.

Regards,

Kaushik Solanki

[#3]

Ok, when making the following selections:

Sector: Automotive

Corporate Group: Group1

Corporate Peer: Group2

I am exptecting the values of the both lower tables to be displayed in the chart without actually changing anythin in the chart.

[b_garside]

Sounds like your asking how to do dynamic aggregation groupings that match the drill down values. You can use the GetCurrentField().  See this posting: https://community.qlik.com/message/721703#721703

[kaushiknsolanki]

Hi,

Hope this is what you are looking for

Regards,

Kaushik Solanki

[#6]

Unfortunately it is not, as far as I understand what you did.

I have tried to illustrate the output I expect in the above file.

[#7]

Yes I think we are getting closer, even though I do not get anything in the link.

However, I have attached another example to illustrate my expected output.

Some explanations:

I have created an alternate state just for testing. I do not want to use it finally.

In the top you see the output I exptect when I want to do analytics on company level (see title of the chart).

Below you can see the output I exptect when doing analytics on any group level (see title of the chart).

In the chart "switch" I have tried to concatenate both levels by implementing conditional dimensions without changing the expressions. When choosing the single companies as in the upper part you get the same result. For the group level, only the group (in this case group1) and not the corporate peer (in this case group2) is displayed in the switch chart.

What am I missing to get group 2 displayed? The best solution for me would be not to change the expression somehow.... (The issue seems to be the "Company=p(Company_Peer)")

[sunny_talwar]

Can you check now

[#9]

Thank you, that it is.

However, this works out on raw data but not on calculated fields. Thus, a few more questions on this:

I have a lot of KPIs calculated.

1. Is there a faster/better way of calculating those in the load script than doing it all over again?

Load
Company,
Sector,
CorporateGroup,
Year,
DataX/DataY as KPI1
Resident Data;


GroupKPIs:
Load 
Sector,
CorporateGroup,
Year,
Sum(DataX)/Sum(DataY) as KPI1_Group
Resident Data Group By Sector, CorporateGroup, Year;

2. In the attached file I have created conditional expressions to switch between KPI1 and KPI1_Group.

Is there an easier solution? Maybe substituting the string "_Group" with a variable? How could I set variable values on that conditions (GetSelectedCount(Company_Peer) <> 0)?

[#10]

Ok, so I think I have found another good solution without touching any chart or expression.

I have loaded the Group as Company in the Load Script. I do have a question on this:

1. Can you apply the sum() operator for the whole  "Group:" load? In my original I have more than 1000 fields in the "data" load, so I would need to do that manually for any field...

2. Why is Group 1 not displayed in the chart? I am pretty sure this is a small mistake but I cannot find it..

Many thanks!