topic Using P() function in graph in QlikView

Using P() function in graph

Tue, 05 Jun 2012 09:26:39 GMT

Hi everybody,

I need to count the dimension “Country_Descr_S” whereof “Country” is related to “Project Category = Execution” and show this value in a chart having dimension “Month”.

I’ve already tried the following formulas:

=COUNT (DISTINCT

{$ < Country_Descr_S= P( {$<[Project Category]={"Execution"} >}Country),

[Project Category]={"Execution"},

Month=P({$}Month)>

}

Country_Descr_S)

=COUNT (DISTINCT

{$ < Country_Descr_S= P( {$<[Project Category]={"Execution"} >}Country),

[Project Category]={"Execution"},

}

Country_Descr_S)

Both them give back the same problem, if I select a single month in the chart filters the value on the single graph bar is ok instead, if I do no selections (multiple months) the value is not split by month in the chart bars but it is the total value.

Do you have any suggestion?

Thanks and regards,

Giampaolo Randisi

Re: Using P() function in graph

swuehl — Tue, 05 Jun 2012 09:43:55 GMT

And if you remove the set element for Country_Desc_S, your count(distinct ...) values are grouped correctly by Month?

Could you post a sample file or your data model here?

Using P() function in graph

Tue, 05 Jun 2012 09:46:49 GMT

Hello Giampaolo,

this is a raw guess, but I think it should be the "1" instead of "$". Within the P() you do not want any user-selection, right ?

P( { 1 <[Project Category]={"Execution"}>} Country)

regards,

Roland

Re: Using P() function in graph

Tue, 05 Jun 2012 10:39:12 GMT

Hello swuehl,

Yes in that case, it groups. But we still need the set analysis .

In attachment you will find a demo of the issue. Filtering graph by april 2013 countries count passes from 7 to 6.

Hello Roland,

The attachment contains your solution and , as you can see, the result is the same.

Any further idea?

Thanks and regards,

Giampaolo Randisi

Re: Using P() function in graph

swuehl — Tue, 05 Jun 2012 10:54:51 GMT

Not sure if I understand your issue, if I clear selections, I do get a degree in Headcount over month, from 8 to 4 over the months.

edit: Tested on QV 11 IR

edit2: Missed your test scenario, please hold on..

Re: Using P() function in graph

Tue, 05 Jun 2012 11:11:32 GMT

Just to clarify:

What i want is to count the number of Country_Descr_S equals to Country associated to Projects with Project Category = Execution split by month of analisys.

The count have to be insensible to Project and Project type selection.

My formula works on the single Month selection. It doesn't work without any month selection.

Re: Using P() function in graph

swuehl — Tue, 05 Jun 2012 13:01:33 GMT

Well, your data model is a bit complicated for me to understand in short time. But I see that your Country and Country_Desc_S are linked via several key fields across multiple tables.

And I believe your set modifier for Country (where you use the p() function) will not guarantee that you get Country_S returned where Country_S equals to Country (well maybe it will work in your setting somehow, but the set modifier in general is not a record based comparison, it's a set assignement).

So I just added an if-clause to make sure you only get matching records:

=count(DISTINCT{$ <

Country_Descr_S= P({1<Project=,

[Project Type]=,

[Project Category]={'Execution'}

, Mese_Anno=P({$}Mese_Anno)

Country)

,[Project Type]=

,[Project Category]={'Execution'}

if(Country=Country_Descr_S,

Country_Descr_S)

)

Not sure if this makes any sense.

Re: Using P() function in graph

Tue, 05 Jun 2012 16:12:25 GMT

Hi Stefan,

The 'If' condition works fine.

I delete P() function also.

Follow the final code:

=count(DISTINCT{$ <

,Project=

,[Project Type]=

,[Project Category]={'Execution'}

if(Country=Country_Descr_S,

Country_Descr_S)

)

Thank you.

Giampaolo

p.s la settanta appatta.