topic Bar Chart with columns table in QlikView

Bar Chart with columns table

danosoft — Wed, 04 Apr 2018 06:52:24 GMT

Hi i am in this situation: i have a table with those columns:

gen_consumo_mensile,feb_consumo_mensile, mar_consumo_mensile,apr_consumo_mensile,mag_consumo_mensile,giu_consumo_mensile, lug_consumo_mensile, ago_consumo_mensile, set_consumo_mensile, ott_consumo_mensile,nov_consumo_mensile,dic_consumo_mensile

Are 12 columns

I want to make a bar chart with, in the dimension i have months (gen,feb, mar,apr,may, jun,jul,ago,sep,oct,nov,dec) and in the bar the sum of those columns (for JAN i want the sum of GEN_CONSUMO_MENSILE, for FEB i want the sum of FEB_CONSUMO_MENSILE, for MAR i want the sum of MAR_CONSUMO_MENSILE.... and so on till for DEC the sum of DIC_CONSUMO_MENSILE.

How can i do that?

Re: Bar Chart with columns table

chinnuchinni — Wed, 04 Apr 2018 07:12:19 GMT

can you please attach the sample data.

Re: Bar Chart with columns table

uacg0009 — Wed, 04 Apr 2018 08:17:58 GMT

Hi Daniele,

if your data is not too large, I think you can use crosstable() directly. Please see my attachment.

Thank.

Aiolos

Re: Bar Chart with columns table

danosoft — Wed, 04 Apr 2018 12:44:55 GMT

Hi Sage, thanks for the reply,... but i think is not the solution for me... i have a table with those field GEN_CONSUMO_MENSILE ETC.... that change when i select an Year and a Month in my visualization.

how can i do a bar chart in this way with those columns?

Re: Bar Chart with columns table

danosoft — Wed, 04 Apr 2018 13:45:04 GMT

This is a sample data where you can do it, thanks

Re: Bar Chart with columns table

danosoft — Wed, 04 Apr 2018 13:45:48 GMT

i need to do with this example so you can understand me

Re: Bar Chart with columns table

danosoft — Thu, 05 Apr 2018 13:00:40 GMT

is impossible to do what i asked?

Re: Bar Chart with columns table

uacg0009 — Sun, 08 Apr 2018 02:51:39 GMT

Hi Daniele,

Sorry to reply late, could you please try to use below script to load again?

CONTR_Gas:

LOAD

"FORNITORE",

"COD_CLIENTE",

"RAGIONE_SOCIALE",

PR_SEDE_LEGALE,

"DES_PR_CLI",

DES_REGIONE_CLI,

"COD_ATECO",

DESCINT_ATECO,

COD_PUNTO,

"CD_TP_PUNTO",

"ID_FORNITURA",

"PDR",

PR_PDR,

"DES_PR_PDR",

DES_REGIONE_PDR,

"COD_CONTRATTO",

"PRODOTTO",

"GAMMA_PRODOTTO",

"COD_AGENZIA",

"CANALE",

"REFERENTE",

"AGENZIA_PADRE",

"AGENZIA",

"AGENTE",

"D_FIRMA",

"D_INIZ_FORN",

"D_FINE_FORN",

"D_INIZ_VAL",

"D_FINE_VAL",

"ANNOINIVAL",

"ANNOFINEVAL",

"MESEINIVAL",

"MESEFINEVAL",

"ANNOINIFORN",

"ANNOFINEFORN",

"MESEINIFORN",

"MESEFINEFORN",

"CONSUMO_TOT_ANNUO",

"MESEFINEVAL_MENOUNO",

"GEN_CONSUMO_MENSILE",

"FEB_CONSUMO_MENSILE",

"MAR_CONSUMO_MENSILE",

"APR_CONSUMO_MENSILE",

"MAG_CONSUMO_MENSILE",

"GIU_CONSUMO_MENSILE",

"LUG_CONSUMO_MENSILE",

"AGO_CONSUMO_MENSILE",

"SET_CONSUMO_MENSILE",

"OTT_CONSUMO_MENSILE",

"NOV_CONSUMO_MENSILE",

"DIC_CONSUMO_MENSILE"

from 'contra.qvd' (qvd);

CrossTable(Month,consumo_mensile,39)

LOAD * Resident CONTR_Gas;

Thanks.

Aiolos

Re: Bar Chart with columns table

danosoft — Mon, 09 Apr 2018 09:33:21 GMT

can you use the example and doing the bar chart with your specifications?, becouse, or i wrong something or i can't load the script

Re: Bar Chart with columns table

uacg0009 — Mon, 09 Apr 2018 09:50:13 GMT

Please see the attachment.

Aiolos

Re: Bar Chart with columns table

danosoft — Mon, 09 Apr 2018 10:01:55 GMT

Thanks you Aiolos but i need to show not sum(consumo_mensile) i need to show

Those 12 columns:

bar chart with, in the dimension i have months (gen,feb, mar,apr,may, jun,jul,ago,sep,oct,nov,dec)

and in the bar the SUM of those columns (for JAN i want the sum of GEN_CONSUMO_MENSILE, for FEB i want the sum of FEB_CONSUMO_MENSILE, for MAR i want the sum of MAR_CONSUMO_MENSILE.... and so on till for DEC the sum of DIC_CONSUMO_MENSILE.

Re: Bar Chart with columns table

uacg0009 — Mon, 09 Apr 2018 10:12:19 GMT

So which column in your file is the "months" you said, I didn't find it.

Re: Bar Chart with columns table

chinnuchinni — Mon, 09 Apr 2018 10:34:32 GMT

take :

dimension: Month

Expression :

=if(Month= 'Jan',sum(GEN_CONSUMO_MENSILE),

if(Month= 'Feb',sum(FEB_CONSUMO_MENSILE),

if(Month = 'Mar',sum(MAR_CONSUMO_MENSILE))))

.........

Re: Bar Chart with columns table

danosoft — Mon, 09 Apr 2018 11:57:55 GMT

Becouse Months, are not a field in my table, months must be fixed

Re: Bar Chart with columns table

danosoft — Tue, 10 Apr 2018 12:47:55 GMT

Can you try to do it in the example i attached? becouse for me it doesn't work

Re: Bar Chart with columns table

uacg0009 — Wed, 11 Apr 2018 02:41:16 GMT

Could you please give a sample and also the desired result you want?

Because I think in my attachment, I just for Jan - sum(gen), Feb - sum(feb). but you said it's not correct.

So could you please tell me the desired result you want? you can make it in excel and screenshot to me.

Re: Bar Chart with columns table

chinnuchinni — Wed, 11 Apr 2018 05:21:43 GMT

Hi Daniele,

please try the bellow.

Dimension:

=ValueList('Jan','Feb','Mar','Apr','May','Jun','Jly','Aug','Sep','Oct','Nov','Dec')

expression:

if(ValueList('Jan','Feb','Mar','Apr','May','Jun','Jly','Aug','Sep','Oct','Nov','Dec')= 'Jan',sum(GEN_CONSUMO_MENSILE),

if(ValueList('Jan','Feb','Mar','Apr','May','Jun','Jly','Aug','Sep','Oct','Nov','Dec')= 'Feb',sum(FEB_CONSUMO_MENSILE),

if(ValueList('Jan','Feb','Mar','Apr','May','Jun','Jly','Aug','Sep','Oct','Nov','Dec')= 'Mar',sum(MAR_CONSUMO_MENSILE),

if(ValueList('Jan','Feb','Mar','Apr','May','Jun','Jly','Aug','Sep','Oct','Nov','Dec')= 'Apr',sum(APR_CONSUMO_MENSILE),

if(ValueList('Jan','Feb','Mar','Apr','May','Jun','Jly','Aug','Sep','Oct','Nov','Dec')= 'May',sum(MAG_CONSUMO_MENSILE),

if(ValueList('Jan','Feb','Mar','Apr','May','Jun','Jly','Aug','Sep','Oct','Nov','Dec')= 'Jun',sum(GIU_CONSUMO_MENSILE),

if(ValueList('Jan','Feb','Mar','Apr','May','Jun','Jly','Aug','Sep','Oct','Nov','Dec')= 'Jly',sum(LUG_CONSUMO_MENSILE),

if(ValueList('Jan','Feb','Mar','Apr','May','Jun','Jly','Aug','Sep','Oct','Nov','Dec')= 'Aug',sum(AGO_CONSUMO_MENSILE),

if(ValueList('Jan','Feb','Mar','Apr','May','Jun','Jly','Aug','Sep','Oct','Nov','Dec')= 'Sep',sum(SET_CONSUMO_MENSILE),

if(ValueList('Jan','Feb','Mar','Apr','May','Jun','Jly','Aug','Sep','Oct','Nov','Dec')= 'Oct',sum(OTT_CONSUMO_MENSILE),

if(ValueList('Jan','Feb','Mar','Apr','May','Jun','Jly','Aug','Sep','Oct','Nov','Dec')= 'Nov',sum(NOV_CONSUMO_MENSILE),

if(ValueList('Jan','Feb','Mar','Apr','May','Jun','Jly','Aug','Sep','Oct','Nov','Dec')= 'Dec',sum(DIC_CONSUMO_MENSILE)

))))))))))))

Re: Bar Chart with columns table

danosoft — Wed, 11 Apr 2018 06:50:24 GMT

Thanks Praveen i think we are near the solution... i attach the example with your script, is ok now for dimension, only in the script not work the Sum total of the expressions...

for Example select 2017 year, i expect those total in the chart :

gen_consumo_mensile = 1374

feb_consumo_mensile = 1035

mar_consumo_mensile = 907

apr_consumo_mensile = 594

and so on....

those are the totals you see in the table above tha chart

thanks

Re: Bar Chart with columns table

uacg0009 — Wed, 11 Apr 2018 07:18:17 GMT

I don't know whether you see my attachment seriously.

my result is:

And you said that's wrong, that's not what you want.

Re: Bar Chart with columns table

danosoft — Wed, 11 Apr 2018 08:25:12 GMT

I don't know becouse using your expression in my sample (attached) it not work

i attached before the sample, can you use it to make it correct?