Set analysis in pivot table

[Anonymous]

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.

[awhitfield]

Sorry, but the data looks correct

Agent1/USA = 10 + 15

Agent2/Italy = 20 + 25

Am I missing something?

[Anonymous]

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.

N.

[jpenuliar]

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?

[Anonymous]

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.

[chrismarlow]

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.

[chrismarlow]

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))
)

[Anonymous]

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))

[petter]

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).

[Anonymous]

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