topic Re: gauge charT in QlikView

gauge charT

manoranjan_d — Fri, 08 Jul 2016 11:08:44 GMT

PAYING_WAY	PAID DETAILS	AMT
BANK	NOTNULL	100
CHEQUE	NULL	200
BANK	NOTNULL	300
CHEQUE	NULL	400
BANK	NOTNULL	500
CHEQUE	NULL	600
BANK	NOTNULL	700
CHEQUE	NULL	800
BANK	NOTNULL	900
CHEQUE	NULL	1000
BANK	NULL	1100
BANK	NULL	1200
BANK	NULL	1300
BANK	NULL	1400

above is my data i need the count for notnull paid details fields for only paying way = bank in percentage wise where the gauge should show the percentage of not null

so i tried the expression its not coming correctly

i used the expression as

=((count({<PAID DETAILS={'NOTNULL'}>}PAYING_WAY))/(Count({<PAID DETAILS={'NULL'}>}PAYING_WAY)+

Count({<PAID DETAILS={'NOTNULL'}>}PAYING_WAY)))*100

can i any one help for the gauage chart

Re: gauge charT

sunny_talwar — Fri, 08 Jul 2016 11:19:41 GMT

What are you getting? and what do you expect to see?

Re: gauge charT

Anonymous — Fri, 08 Jul 2016 11:21:50 GMT

Are you looking for 5 NOTNULL paid details divided by the total 9 paying way of BANK which equals 55.55%? Also with you gauge chart you have not selected a dimension have you as this will affect the calculation?

Re: gauge charT

manoranjan_d — Fri, 08 Jul 2016 11:29:01 GMT

yes 5 not null divided by all count of 9 paying way bank of bank, equals to 55.55

i dont know how to use the diemsnion for the specified paying way = bank, i used calcuated dimesniosn as paying way = bank,

it shwing show other percentegae

Re: gauge charT

sunny_talwar — Fri, 08 Jul 2016 11:31:13 GMT

May be try this:

=((Count({<PAID DETAILS={'NOTNULL'}, PAYING_WAY = {'BANK'}>}PAYING_WAY))/(Count({<PAID DETAILS={'NULL'}, PAYING_WAY = {'BANK'}>}PAYING_WAY) +

Count({<PAID DETAILS={'NOTNULL'}, PAYING_WAY = {'BANK'}>}PAYING_WAY)))*100

Re: gauge charT

manoranjan_d — Fri, 08 Jul 2016 12:11:49 GMT

how to format this to 55.55%

Re: gauge charT

sunny_talwar — Fri, 08 Jul 2016 12:13:50 GMT

Try this:

Num(

((Count({<PAID DETAILS={'NOTNULL'}, PAYING_WAY = {'BANK'}>}PAYING_WAY))/(Count({<PAID DETAILS={'NULL'}, PAYING_WAY = {'BANK'}>}PAYING_WAY) +

Count({<PAID DETAILS={'NOTNULL'}, PAYING_WAY = {'BANK'}>}PAYING_WAY)))*100, '##.00%')

or remove *100

Num(

((Count({<PAID DETAILS={'NOTNULL'}, PAYING_WAY = {'BANK'}>}PAYING_WAY))/(Count({<PAID DETAILS={'NULL'}, PAYING_WAY = {'BANK'}>}PAYING_WAY) +

Count({<PAID DETAILS={'NOTNULL'}, PAYING_WAY = {'BANK'}>}PAYING_WAY))), '##.00%')

Re: gauge charT

Anonymous — Fri, 08 Jul 2016 12:22:48 GMT

Nice solution Sunny. I would favour your second expression without the multiplication by 100.

Kindest regards

Brian

Re: gauge charT

manoranjan_d — Fri, 08 Jul 2016 12:25:01 GMT

now they want the amount in pie chart where it should show the null value amount and not null value amout, so how to make the format for amount in dollars example $22.5K

Re: gauge charT

sunny_talwar — Fri, 08 Jul 2016 12:30:00 GMT

May be like this:

Num(Sum({<PAYING_WAY = {'BANK'}>} AMT)/1000, '$#,##0.0K')

Re: gauge charT

manoranjan_d — Fri, 08 Jul 2016 12:31:12 GMT

i tried to remove the 100 in the expression but it showing incorrectly display of the gauage , but diaplying the 55.55% is perfect in presentastion tab

Re: gauge charT

manoranjan_d — Fri, 08 Jul 2016 12:40:10 GMT

if amount was huge like million and billions

Re: gauge charT

sunny_talwar — Fri, 08 Jul 2016 12:46:32 GMT

You can do an if statement:

If(Sum({<PAYING_WAY = {'BANK'}>} AMT) > 1000000000,

Num(Sum({<PAYING_WAY = {'BANK'}>} AMT)/1000000000, '$#,##0.0B'),

If(Sum({<PAYING_WAY = {'BANK'}>} AMT) > 1000000,

Num(Sum({<PAYING_WAY = {'BANK'}>} AMT)/1000000, '$#,##0.0M'),

If(Sum({<PAYING_WAY = {'BANK'}>} AMT) > 1000,

Num(Sum({<PAYING_WAY = {'BANK'}>} AMT)/1000, '$#,##0.0K', '$#,##0.0)))