Chart with two alternative dimensions

[fabio]

Let's begin with an example:

LOAD * inline [

Customer|Agente_BDG|Bdg

Cus1|Ag1|40
Cus2|Ag1|42
Cus3|Ag2|60
Cus4|Ag1|13
Cus5|Ag2|45
Cus6|Ag1|78

] (delimiter is '|');

LOAD * inline [

Customer|Agent

Cus1|Ag2
Cus2|Ag2
Cus3|Ag2
Cus4|Ag1
Cus5|Ag1
Cus6|Ag1

] (delimiter is '|');

If i sum the bdg values by the Agent dimension i get:

Ag1 136

Ag2 142

If i sum instead the bdg values by Agente_BDG dimension i get:

Ag1 173

Ag2 105

I'd like to create a graph like this:

Ag1 136 173

Ag2 142 105

I put in the chart the dimenson "Agent" and,

in the first column the expression "=SUM(Bdg)"

in the second colum some similar to "=aggr( sum( total Bdg),Agente_BDG)"

Obiuvsly the last expression doesn't work.

Any help for me? Thanks in advance!!!

[sunny_talwar]

Not sure if script modification can be made or not, but one way would be to create an island table like this

Table1:
LOAD * INLINE [
    Customer, Agente_BDG, Bdg
    Cus1, Ag1, 40
    Cus2, Ag1, 42
    Cus3, Ag2, 60
    Cus4, Ag1, 13
    Cus5, Ag2, 45
    Cus6, Ag1, 78
];

Table2:
LOAD * INLINE [
    Customer, Agent
    Cus1, Ag2
    Cus2, Ag2
    Cus3, Ag2
    Cus4, Ag1
    Cus5, Ag1
    Cus6, Ag1
];

IslandTable:
LOAD DISTINCT Agente_BDG as IslandAgent
Resident Table1;

Concatenate (IslandTable)
LOAD DISTINCT Agent as IslandAgent
Resident Table2
Where not Exists(Agente_BDG, Agent);

and then use IslandAgent as dimension and following expressions

Sum(If(IslandAgent = Agent, Bdg))
Sum(If(IslandAgent = Agente_BDG, Bdg))

[fabio · Accepted solution]

Thanks for your idea!

meantime i found another way, i created a table summing the values at agent level with the correct grouping, like this :

Budget_grouped:
LOAD
Agente_BDG as Agente,
sum(Bdg) as BdgSummed
resident Budget
group by Agente_BDG;

I'm going to do some test to find the best way for the user selections, analisys and so on..

thanks again

[sunny_talwar]

Sounds good, let us know what you end up doing.

Best,
Sunny