topic Re: How to test which of the levels of a drill down group is shown? in QlikView

How to test which of the levels of a drill down group is shown?

rudywelvaert — Tue, 09 Feb 2016 13:47:46 GMT

Hi,

I use a drill down group with the name: Team_Responsible_Amount.

It contains two calculated fields:

=If($(vLanguage)='F', TEAM_F, TEAM_E)

and

=If(RESPONSIBLE_FIRST_NAME = RESPONSIBLE_LAST_NAME, RESPONSIBLE_LAST_NAME, RESPONSIBLE_FIRST_NAME & ' ' & RESPONSIBLE_LAST_NAME)

In a pivot table with one of the dimensions Team_Responsible_Amount and the other year_month, the sum of amounts is shown per responsible or per team and per month.

If the team is shown, the background color should be set when the sum of the amounts is > vMaxTeam,

Drilling down to the responsible, the background color should be set when the sum of the amounts is > vMaxResponsible.

Example:

SET vMaxTeam= 500000;

SET vMaxResponsible= 75000;

How can I test which of the two levels of my group Team_Responsible_Amount is shown so that I can compare the sum respectively with vMaxTeam or vMaxResponsible

As a test I used following expression for the background color, but with no result:

=If ((Dimensionality()=1) and (Index(GetCurrentField(Team_Responsible_Amount),'TEAM')>0), rgb(255,180,180))

Thanks

Re: How to test which of the levels of a drill down group is shown?

Gysbert_Wassenaar — Tue, 09 Feb 2016 17:23:45 GMT

You can use the GetCurrentField function to check which level of a group is active. If you're having problems getting it to work then post a small qlikview document that demonstrates the problem.

Re: How to test which of the levels of a drill down group is shown?

rudywelvaert — Wed, 10 Feb 2016 12:51:49 GMT

Thanks Gysbert,

I did use the GetCurrentField() function. But as I indicated the fields in my drill down group are expressions, not real fields.

What does the GetCurrentField() give back when the fields are calculated fields, and how do you test which of the fields is shown?

Included is an strongly simplified version of my project.

With

SET vLanguage = 'E';

SET vMaxTeam= 170000;

SET vMaxResponsible= 40000;

and as definition of the expression back color:

=If ((Dimensionality()=1) and (Index(GetCurrentField('Team_Responsible_Amount'),'TEAM')>0),

If(Sum(AMOUNT)>$(vMaxTeam),rgb(255,180,180)),

If(Sum(AMOUNT)>$(vMaxResponsible),rgb(255,180,180)))

I get this as a result:

But I want this as a result:

Thanks

R.W.

Re: How to test which of the levels of a drill down group is shown?

rudywelvaert — Wed, 10 Feb 2016 13:25:20 GMT

Using

=If (Dimensionality()=1,

If(Index(GetCurrentField('Team_Responsible_Amount'),'TEAM')>0,

If(Sum(AMOUNT)>$(vMaxTeam),rgb(255,180,180)),

If(Sum(AMOUNT)>$(vMaxResponsible),rgb(255,180,180))))

instead of

=If ((Dimensionality()=1) and (Index(GetCurrentField('Team_Responsible_Amount'),'TEAM')>0),

If(Sum(AMOUNT)>$(vMaxTeam),rgb(255,180,180)),

If(Sum(AMOUNT)>$(vMaxResponsible),rgb(255,180,180)))

removes the background color from the total lines.

Still the GetCurrentField() function doesn't give me the level of the drill down.

Re: How to test which of the levels of a drill down group is shown?

Gysbert_Wassenaar — Wed, 10 Feb 2016 17:55:02 GMT

The GetCurrentField function does work with the expressions you use in the drill down group. But it does not return the label you gave the expressions, but the expressions themselves. Those expression texts aren't very useful, but you can use the length of those expression texts:

=If ((Dimensionality()=1) and (len(GetCurrentField(Team_Responsible_Amount))=37),

If(Sum(AMOUNT)>$(vMaxTeam),rgb(255,180,180)),

If(Sum(AMOUNT)>$(vMaxResponsible),rgb(255,180,180)))

Re: How to test which of the levels of a drill down group is shown?

rudywelvaert — Thu, 11 Feb 2016 09:42:02 GMT

Thanks Gysbert

it works