Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

Quartile expression graph bar problem

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

BarGraph.png

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

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