topic Re: Bar chart using ranges as dimension in QlikView

Bar chart using ranges as dimension

JoaoBordignon — Sat, 16 Nov 2024 01:09:34 GMT

Maybe I'm searching using the wrong terms, but I can't find a solution.

I need to create a bar chart whose dimensions are a series of ranges and the expression the number of projects that fall in that ranges.

For example I have two tables:

Projects

Project_name	Tasks
Project 1	20
Project 2	15
Project 3	5
Project 4	30
Project 5	25

and

Tasks_done

Project_name	tasks_done
Project 1	10
Project 2	7
Project 3	5
Project 4	3

I need to create a chart that looks like this:

Number of projects by completion %

2 _____________ ______________

1 ___________ |$$$$$$$$$| |##########|

|^^^^^^^^^| |$$$$$$$$$| |##########|

100% to 70% | 70% to 40% | less than 40%

Anyone can point me to the right direction? I'm using QlikView 12, if it helps.

Thanks!

Re: Bar chart using ranges as dimension

NitinK7 — Fri, 28 Feb 2020 18:10:34 GMT

Hi,

try following

first create range

ABC:
LOAD *,
if(Num(tasks_done/Tasks)>0.7 and Num(tasks_done/Tasks)<=1,Dual('100% to 70%',1),
if(Num(tasks_done/Tasks)>0.4 and Num(tasks_done/Tasks)<=0.7, Dual('70% to 40%',2),
if(Num(tasks_done/Tasks)<=0.4,Dual('Less than 40%',3)))) as taskrange ;

LOAD Project_name,
Tasks,
tasks_done
FROM
data.xlsx
(ooxml, embedded labels, table is sample);

and then use barchart object take taskrange as dimension and use expression count (Project_name)

Re: Bar chart using ranges as dimension

JoaoBordignon — Tue, 03 Mar 2020 12:52:31 GMT

Hi, thanks for the response.

But I'm having trouble making the range, I try but taskrange (faixa_tipificados) never appears as a dimension.

If I try to store the results from ABC into a file, it complains that there are no table named ABC.

The data is in two separated tables QTD_ATRIBUTOS_TIPIFICADOS is in qualidade and QUANTIDADE_COLUNAS in indicadores.

My load script is this:

LOAD * FROM [$(caminhoArquivoEntrada)\Qualidade.qvd] (qvd);

LOAD * FROM [$(caminhoArquivoEntrada)\ClientesProdutos.qvd] (qvd);

LOAD * FROM [$(caminhoArquivoEntrada)\Indicadores.qvd] (qvd);

LOAD * FROM [$(caminhoArquivoEntrada)\LogsUCD.qvd] (qvd);

LOAD * FROM [$(caminhoArquivoEntrada)\Calendario.qvd] (qvd);

LOAD * FROM [$(caminhoArquivoEntrada)\Baselines.qvd] (qvd);

LOAD * FROM [$(caminhoArquivoEntrada)\CalendarioBaselines.qvd] (qvd);

LOAD * FROM [$(caminhoArquivoEntrada)\Qualidade.qvd] (qvd);

LOAD * FROM [$(caminhoArquivoEntrada)\Indicadores.qvd] (qvd);

ABC:
LOAD *,
if(Num(QTD_ATRIBUTOS_TIPIFICADOS/QUANTIDADE_COLUNAS )>0.8 and Num(QTD_ATRIBUTOS_TIPIFICADOS/QUANTIDADE_COLUNAS)<=1,Dual('100% a 80%', 1),
if(Num(QTD_ATRIBUTOS_TIPIFICADOS/QUANTIDADE_COLUNAS )>0.6 and Num(QTD_ATRIBUTOS_TIPIFICADOS/QUANTIDADE_COLUNAS)<=0.8,Dual('80% a 60%', 2),
if(Num(QTD_ATRIBUTOS_TIPIFICADOS/QUANTIDADE_COLUNAS )>0.4 and Num(QTD_ATRIBUTOS_TIPIFICADOS/QUANTIDADE_COLUNAS)<=0.6,Dual('60% a 40%', 3),
if(Num(QTD_ATRIBUTOS_TIPIFICADOS/QUANTIDADE_COLUNAS )>0.2 and Num(QTD_ATRIBUTOS_TIPIFICADOS/QUANTIDADE_COLUNAS)<=0.4,Dual('40% a 20%', 4),
if(Num(QTD_ATRIBUTOS_TIPIFICADOS/QUANTIDADE_COLUNAS)<=0.2, Dual('menor que 20%', 5)))))) as faixa_tipificados;

Re: Bar chart using ranges as dimension

NitinK7 — Tue, 03 Mar 2020 13:09:31 GMT

Hi, Yes, You have not get any range because of your script in table ABC qlikview unable to find "QTD_ATRIBUTOS_TIPIFICADOS" and "QUANTIDADE_COLUNAS" this two fields Could you please tell me the name of the QVD for getting "QTD_ATRIBUTOS_TIPIFICADOS" and "QUANTIDADE_COLUNAS" this two fields then I will provide you exact Answer.

Re: Bar chart using ranges as dimension

NitinK7 — Tue, 03 Mar 2020 13:17:33 GMT

I recommended you to store QTD_ATRIBUTOS_TIPIFICADOS" and "QUANTIDADE_COLUNAS" this two fields

in one table then

load this table and write range logic using precedent load

see below example

Load

your range logic;

load

QTD_ATRIBUTOS_TIPIFICADOS,

QUANTIDADE_COLUNAS

from table name;

regards,

Nitin.

Re: Bar chart using ranges as dimension

JoaoBordignon — Tue, 03 Mar 2020 13:19:33 GMT

QTD_ATRIBUTOS_TIPIFICADOS in in "Qualidade.qvd" and QUANTIDADE_COLUNAS is in "Indicadores.qvd"

🙂