Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
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

0 Replies