topic Re: Set analysis in pivot table in QlikView

Set analysis in pivot table

Anonymous — Tue, 24 Mar 2015 14:37:41 GMT

Hi guys,

in the attached example I have two tables:

- The first one contains the transfert day for each agent referred to the past

- The second one contains the states linked nowadays to every agent

I'd like to have a cross table which shows (for every state linked to every agent) the sum of the days spent in the state in the past

In the example the combination Agente1/USA should show 30 days and the Agente2/Italy should shows 25.

I've tried to do it with a set analysis formula (you can see it in the second line of the expression of the pivot table), but it doesn't work.

Should someone help me, please?

Thank you.

Nick.

Re: Set analysis in pivot table

awhitfield — Tue, 24 Mar 2015 14:58:11 GMT

Sorry, but the data looks correct

Agent1/USA = 10 + 15

Agent2/Italy = 20 + 25

Am I missing something?

Re: Set analysis in pivot table

Anonymous — Tue, 24 Mar 2015 15:06:26 GMT

Sorry, evidently I haven't been clear enough...for each agent at the present linked at every state I'd like to show the sum of the transfer days of the past in that state (regardless of the agent).

So:

- for the USA now linked to Agente1 it must shows 30 day, the sum of the days made in the past both by Agente1 (10) and Agente2 (20)

- for the Italy now linked to Agente2 it must shows 25 days, the sum of the days made in the past both by Agente1 (0, in this case) and Agente2 (25)

Hope it's clearest now.

Re: Set analysis in pivot table

jpenuliar — Tue, 24 Mar 2015 15:13:54 GMT

What is the difference between DescStato and DescStatoAssegnato,

If I use DescStato, I can see the data you are expecting, Do you mean to exclude China figures on DescStato?

Re: Set analysis in pivot table

Anonymous — Tue, 24 Mar 2015 15:33:18 GMT

DescStato is the description of the state where in the past the transferts have been made, DescStatoAssegnato is the description of the state assigned nowadays to a single agent.

Re: Set analysis in pivot table

chrismarlow — Tue, 24 Mar 2015 15:34:04 GMT

I am not sure if this is going to work as I guess your code here is a snippet, but if you switch your model, so it associates on DescStato;

Load * inline [
DescStato,NomeAreaManager, GiorniTrasferta
'USA', 'Agente1', 10
'China', 'Agente1', 15
'USA', 'Agente2', 20
'Italy','Agente2',25
];

Load * inline [
DescStato,NomeAreaManagerAssegnato
'USA', 'Agente1'
'Italy','Agente2'
];

An only(NomeAreaManagerAssegnato) function works. Obviously depends on your ability to change the code.

Re: Set analysis in pivot table

chrismarlow — Tue, 24 Mar 2015 15:48:02 GMT

Or without changing your model, try this in a pivot table expression;

If(FieldIndex('DescStatoAssegnato',Only(DescStato))=0,
Null(),
Firstsortedvalue(NomeAreaManager,If(DescStatoAssegnato=DescStato,1,2))
)

Re: Set analysis in pivot table

Anonymous — Tue, 24 Mar 2015 16:24:37 GMT

Your solution in this case it's applicable and solves the problem...so thak you very much 🙂

The general solution with the set analysis I was trying to apply isn't feasible?

If(IsNull(DescStatoAssegnato),'',Sum({$<DescStato = {DescStatoAssegnato}>} GiorniTrasferta))

Re: Set analysis in pivot table

petter — Tue, 24 Mar 2015 17:01:59 GMT

If you are able to rearrange your data model by connecting the two tables on country instead of agent

you can get away with a very simple set expression (to get rid of China that doesn't have a current responsible).

Re: Set analysis in pivot table

Anonymous — Wed, 25 Mar 2015 08:55:02 GMT

ok, but what about if I'd must keep the connection between the agents? Is it possible to extract the same data by linking the countries by means of the set analysis (something like what I was trying to do: If(IsNull(DescStatoAssegnato),'',Sum({$<DescStato = {DescStatoAssegnato}>} GiorniTrasferta))?

I'd like to understand it for further applications...

Thks