Bar chart using ranges as dimension

[JoaoBordignon]

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!

[NitinK7]

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)

[JoaoBordignon]

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;

[NitinK7]

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.

[NitinK7]

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.

[JoaoBordignon]

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

🙂