Graph YTD total - aggr set analysis not working

Sun, 22 Feb 2015 16:28:47 GMT

Hi,

First of all I had the following problem with a pivot table, I have real sales data by country for different month: each row was a different country. The total was supossed to be the total for all countries but the total result was the max value of each row not the sum of rows. For example:

January

Spain 5

France 2

Total 5

Instead of :

January

Spain 5

France 2

Total 7

The solutions was the following: use AGGR(SET ANALYSIS)Country, Month

I had the same problem for Graphs where I only had Month dimension, I triedd wit set anlysis, aggr function, total function but the total only work for simple realt data but it is not right for complex forecast data because the total that appears is the total of Spain.

It is not possible to use aggr function and set analysis for a graph with complex data as a forecast YTD average for each month (Fact1/Fact2). Any solution?

There was a solution for bar graph (not fore line graph). Use Month dimension and Country dimension: show the total like: France, Spain and Total. Then I colour blanck thedimension Country and the different countries so I can show only Total. The problem of using this is that the format of the graph have change: Now I have stacking bars instead of one bar for each calculated fact.

So I couldn't make possible a Forecast YTD graph. The question are the following.

1.Is it not possible to use aggr function and set analysis for a graph with complex data as a forecast YTD average for each month. The calculated fact is like (Fact/Fact1) / (Fact2/Fact3).- Fact2/Fact3).- Any solution? I tried a lot of times and it doesn't works. How can I use total or dimesionality in it?

I only could make possible the total of one Country whit this formula:

IF(ValueList

('Ene','Feb','Mar','Abr','May','Jun','Jul','Ago','Sep','Oct','Nov','Dic')='Ene',

sum( {<Mes, ESTM_MES={01}>} OCUPACION)

/sum( {<Mes, ESTM_MES={01}>} ESTM_CH_MES)

IF(ValueList ('Ene','Feb','Mar','Abr','May','Jun','Jul','Ago','Sep','Oct','Nov','Dic')='Feb',

if($(vMesSelec)<= 02,

(sum( {<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' & $(vMesSelec) )"}>} OCUPACION)/sum( {<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' & $(vMesSelec) )"}>} ESTM_CH_MES))

(sum({<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' &'02' )"}>} OCUPACION)/sum({<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' &'02' )"}>} ESTM_CH_MES))

)

+ if($(vMesSelec)< 02,

(sum( {<Mes, ESTM_MES={"$(= '>' & $(vMesSelec) & '<=' & '02')"}>} OCUPACION_PRESU)/sum( {<Mes, ESTM_MES={"$(= '>' & $(vMesSelec) & '<=' & '02')"}>} PRESU_COCHES_MES))

,0)

IF(ValueList ('Ene','Feb','Mar','Abr','May','Jun','Jul','Ago','Sep','Oct','Nov','Dic')='Mar',

if($(vMesSelec)<= 03,

(sum( {<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' & $(vMesSelec) )"}>} OCUPACION)/sum( {<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' & $(vMesSelec) )"}>} ESTM_CH_MES))

(sum({<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' &'03' )"}>} OCUPACION)/sum({<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' &'03' )"}>} ESTM_CH_MES))

)

+ if($(vMesSelec)< 03,

(sum( {<Mes, ESTM_MES={"$(= '>' & $(vMesSelec) & '<=' & '03')"}>} OCUPACION_PRESU)/sum( {<Mes, ESTM_MES={"$(= '>' & $(vMesSelec) & '<=' & '03')"}>} PRESU_COCHES_MES))

,0),

IF(ValueList ('Ene','Feb','Mar','Abr','May','Jun','Jul','Ago','Sep','Oct','Nov','Dic')='Abr',

if($(vMesSelec)<= 04,

sum( {<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' & $(vMesSelec) )"}>} OCUPACION)/ sum( {<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' & $(vMesSelec) )"}>} ESTM_CH_MES)

sum({<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' &'04' )"}>} OCUPACION)/sum({<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' &'04' )"}>} ESTM_CH_MES)

)

+ if($(vMesSelec)< 04,

sum( {<Mes, ESTM_MES={"$(= '>' & $(vMesSelec) & '<=' & '04')"}>} OCUPACION_PRESU)

/sum( {<Mes, ESTM_MES={"$(= '>' & $(vMesSelec) & '<=' & '04')"}>} PRESU_COCHES_MES)

,0),

IF(ValueList ('Ene','Feb','Mar','Abr','May','Jun','Jul','Ago','Sep','Oct','Nov','Dic')='May',

if($(vMesSelec)<= 05,

(sum( {<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' & $(vMesSelec) )"}>} OCUPACION)/sum( {<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' & $(vMesSelec) )"}>} ESTM_CH_MES))

(sum({<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' &'05' )"}>} OCUPACION)/sum({<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' &'05' )"}>} ESTM_CH_MES))

)

+ if($(vMesSelec)< 05,

(sum( {<Mes, ESTM_MES={"$(= '>' & $(vMesSelec) & '<=' & '05')"}>} OCUPACION_PRESU)/sum( {<Mes, ESTM_MES={"$(= '>' & $(vMesSelec) & '<=' & '05')"}>} PRESU_COCHES_MES))

,0),

IF(ValueList ('Ene','Feb','Mar','Abr','May','Jun','Jul','Ago','Sep','Oct','Nov','Dic')='Jun',

if($(vMesSelec)<= 06,

(sum( {<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' & $(vMesSelec) )"}>} OCUPACION)/sum( {<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' & $(vMesSelec) )"}>} ESTM_CH_MES))

(sum({<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' &'06' )"}>} OCUPACION)/sum({<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' &'06' )"}>} ESTM_CH_MES))

)

+ if($(vMesSelec)< 06,

(sum( {<Mes, ESTM_MES={"$(= '>' & $(vMesSelec) & '<=' & '06')"}>} OCUPACION_PRESU)/sum( {<Mes, ESTM_MES={"$(= '>' & $(vMesSelec) & '<=' & '06')"}>} PRESU_COCHES_MES))

,0),

IF(ValueList ('Ene','Feb','Mar','Abr','May','Jun','Jul','Ago','Sep','Oct','Nov','Dic')='Jul',

if($(vMesSelec)<= 07,

(sum( {<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' & $(vMesSelec) )"}>} OCUPACION)/sum( {<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' & $(vMesSelec) )"}>} ESTM_CH_MES))

(sum({<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' &'07' )"}>} OCUPACION)/sum({<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' &'07' )"}>} ESTM_CH_MES))

)

+ if($(vMesSelec)< 07,

(sum( {<Mes, ESTM_MES={"$(= '>' & $(vMesSelec) & '<=' & '07')"}>} OCUPACION_PRESU)/sum( {<Mes, ESTM_MES={"$(= '>' & $(vMesSelec) & '<=' & '07')"}>} PRESU_COCHES_MES))

,0),

IF(ValueList ('Ene','Feb','Mar','Abr','May','Jun','Jul','Ago','Sep','Oct','Nov','Dic')='Ago',

if($(vMesSelec)<= 08,

(sum( {<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' & $(vMesSelec) )"}>} OCUPACION)/sum( {<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' & $(vMesSelec) )"}>} ESTM_CH_MES))

(sum({<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' &'08' )"}>} OCUPACION)/sum({<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' &'08' )"}>} ESTM_CH_MES))

)

+ if($(vMesSelec)< 08,

(sum( {<Mes, ESTM_MES={"$(= '>' & $(vMesSelec) & '<=' & '08')"}>} OCUPACION_PRESU)/sum( {<Mes, ESTM_MES={"$(= '>' & $(vMesSelec) & '<=' & '08')"}>} PRESU_COCHES_MES))

,0),

IF(ValueList ('Ene','Feb','Mar','Abr','May','Jun','Jul','Ago','Sep','Oct','Nov','Dic')='Sep',

if($(vMesSelec)<= 09,

(sum( {<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' & $(vMesSelec) )"}>} OCUPACION)/sum( {<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' & $(vMesSelec) )"}>} ESTM_CH_MES))

(sum({<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' &'09' )"}>} OCUPACION)/sum({<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' &'09' )"}>} ESTM_CH_MES))

)

+ if($(vMesSelec)< 09,

(sum( {<Mes, ESTM_MES={"$(= '>' & $(vMesSelec) & '<=' & '09')"}>} OCUPACION_PRESU)/sum( {<Mes, ESTM_MES={"$(= '>' & $(vMesSelec) & '<=' & '09')"}>} PRESU_COCHES_MES))

,0),

IF(ValueList ('Ene','Feb','Mar','Abr','May','Jun','Jul','Ago','Sep','Oct','Nov','Dic')='Oct',

if($(vMesSelec)<= 10,

(sum( {<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' & $(vMesSelec) )"}>} OCUPACION)/sum( {<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' & $(vMesSelec) )"}>} ESTM_CH_MES))

(sum({<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' &'10' )"}>} OCUPACION)/sum({<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' &'10' )"}>} ESTM_CH_MES))

)

+ if($(vMesSelec)< 09,

(sum( {<Mes, ESTM_MES={"$(= '>' & $(vMesSelec) & '<=' & '10')"}>} OCUPACION_PRESU)/sum( {<Mes, ESTM_MES={"$(= '>' & $(vMesSelec) & '<=' & '10')"}>} PRESU_COCHES_MES))

,0),

IF(ValueList ('Ene','Feb','Mar','Abr','May','Jun','Jul','Ago','Sep','Oct','Nov','Dic')='Nov',

if($(vMesSelec)<= 11,

(sum( {<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' & $(vMesSelec) )"}>} OCUPACION)/sum( {<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' & $(vMesSelec) )"}>} ESTM_CH_MES))

(sum({<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' &'11' )"}>} OCUPACION)/sum({<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' &'11' )"}>} ESTM_CH_MES))

)

+ if($(vMesSelec)< 11,

(sum( {<Mes, ESTM_MES={"$(= '>' & $(vMesSelec) & '<=' & '11')"}>} OCUPACION_PRESU)/sum( {<Mes, ESTM_MES={"$(= '>' & $(vMesSelec) & '<=' & '11')"}>} PRESU_COCHES_MES))

,0),

IF(ValueList ('Ene','Feb','Mar','Abr','May','Jun','Jul','Ago','Sep','Oct','Nov','Dic')='Dic',

if($(vMesSelec)<= 12,

(sum( {<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' & $(vMesSelec) )"}>} OCUPACION)/sum( {<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' & $(vMesSelec) )"}>} ESTM_CH_MES))

(sum({<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' &'12' )"}>} OCUPACION)/sum({<Mes, ESTM_MES={"$(= '>=' & '01' & '<=' &'12' )"}>} ESTM_CH_MES))

)

+ if($(vMesSelec)< 12,

(sum( {<Mes, ESTM_MES={"$(= '>' & $(vMesSelec) & '<=' & '12')"}>} OCUPACION_PRESU)/sum( {<Mes, ESTM_MES={"$(= '>' & $(vMesSelec) & '<=' & '12')"}>} PRESU_COCHES_MES))

,0),

)

)))))

)))

2. How can I have a graph with two dimension with not stacking bars.

Right now the graph look like that and we don't want stacking bars:

Any help?

Thank you very much!!

topic Graph YTD total - aggr set analysis not working in QlikView

Graph YTD total - aggr set analysis not working