Quartile expression graph bar problem

Wed, 30 Apr 2014 16:49:12 GMT

Hi Dear QV Community

I have a problem with a Bar Graph, this is the scenario, I have the following dimentions:

“ID_TECNICO_ANTERIOR”: which corresponds to code for technical agent
Año_Mes: Is a field from calendar builded by Month(FieldDate) &” ” Year(FieldDate)

And I have one expression that calculate the number of repetitions in percent, called “% Repeticiones”, the formula is

IF(IsNull(count({<Reiterativo={'SI'},Año = {$(=Max(Año))}>}PEDIDO_ID)/SUM({<Año = {$(=Max(Año))}>} DISTINCT Total_danos)),0,
count({<Reiterativo={'SI'},Año = {$(=Max(Año))}

>}PEDIDO_ID)
/
SUM(DISTINCT {<Año = {$(=Max(Año))},Reiterativo={'SI'}
>} Total_danos)
)

If you check the attached file (“SampleData.xls”), you can verify the result of “% Repeticiones” expression, now check the column “Quartil”, this column is an expression that determinate the Quartile based on the quantity of “ID_TECNICO_ANTERIOR” and order by result of “% Repeticiones” descending. In the sample data are 888 ID_TECNICO_ANTERIOR, for this reason if you divided by 4, for each 222 ID_TECNICO_ANTERIOR there are one quartile. The formula for calculate the Quartile is “CEIL((RowNo()/NoOfRows()*4)) “

For the table works fine, but when I want obtain the same calculation but in a Graph bar, with the two dimentions (Año_Mes and Quartil) and AVG of “% Repeticiones” like to this

The formula for Quartile dimension are “=Aggr(CEIL((RowNo()/NoOfRows()*4)),Año_Mes,ID_TECNICO_ANTERIOR) ”

The formula for expression are

Avg(Aggr(
IF(IsNull(count({<Reiterativo={'SI'},Año = {$(=Max(Año))}>}PEDIDO_ID)/SUM({<Año = {$(=Max(Año))}>} DISTINCT Total_danos)),0,
count({<Reiterativo={'SI'},Año = {$(=Max(Año))}
>}PEDIDO_ID)/
SUM(DISTINCT {<Año = {$(=Max(Año))},Reiterativo={'SI'}
>} Total_danos)
)
,Año_Mes,ID_TECNICO_ANTERIOR)
)

The problem are in the Dimention (Quartile) AGGR Array, because the order of ID_TECNICO_ANTERIOR are based in ID_TECNICO_ANTERIOR but not in result of “% Repeticiones” expression, for this reason the Quartil are not wotking well, because the allocation is made in the order of ID_TECNICO_ANTERIOR and not in result of “% Repeticiones”.

i.e.

Wrong Allocaton of Quartil (Based on order by ID_TECNICO_ANTERIOR), this sort methodology is used by Dimention (Quartile) AGGR Array in the Bar Graph

ID_TECNICO_ANTERIOR	Quartil	% Repetidos
0	1	20,5%
3	1	52,5%
18	1	19,0%
19	1	24,0%
20	1	31,6%
21	1	37,3%
25	1	27,3%
27	1	20,8%
30	1	22,5%
56	1	34,0%
60	1	48,6%

Correct Allocaton of Quartil (Based on order by result of “% Repeticiones” descending), I need use this sort methodology in Dimention (Quartile) AGGR Array to correct allocation of Quartile

ID_TECNICO_ANTERIOR	Quartil	% Repetidos
98713455	1	2,5%
80114962	1	3,1%
1017134290	1	6,8%
1093762316	1	7,0%
80003757	1	7,7%
98602389	1	8,8%
14607626	1	9,3%
1128449965	1	10,1%
809010	1	10,4%
71332326	1	10,5%
11510524	1	10,9%

If you need more information or explication, please let me know

Thanks

topic Quartile expression graph bar problem in QlikView

Quartile expression graph bar problem