topic Re: Chart with two alternative dimensions in QlikView

Chart with two alternative dimensions

fabio — Thu, 31 Oct 2019 13:58:03 GMT

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

] (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!!!

Re: Chart with two alternative dimensions

sunny_talwar — Thu, 31 Oct 2019 17:15:08 GMT

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

Re: Chart with two alternative dimensions

fabio — Mon, 04 Nov 2019 13:48:32 GMT

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

Re: Chart with two alternative dimensions

sunny_talwar — Mon, 04 Nov 2019 14:05:44 GMT

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

Best,
Sunny