newbie data presentation

[#1]

i think my qv skills have regressed since i've used it last. having trouble doing something basic right now i think, please advise? my entire data set is as follows:

account,start_dt,term_dt
1234,1/1/2010,1/11/2010
1233,12/31/2009,
1244,1/5/2010,1/15/2010
1257,1/10/2010,1/11/2010
1261,1/20/2010,1/24/2010

i am trying to do 2 things.

1. make a pie chart showing showing the percent of accounts still open vs. closed. i created a pie with

dimension: =if(IsNull(term_dt), 'Retained', 'Defected')

expression: =count(policy)

this is not working out, just making 1 big blue pie chart of all defected.

2. trying to make a bar graph with percentage accounts closed by days it took to close.

dimension: =term_dt-complaint_dt

expression: count(policy)/5

my problems here are

i.) i am not sure how to count 5 rather than hard code it and

ii.) i get a big chunk at the end for null if the account hasn't closed - can i remove it somehow?

[#2 · Accepted solution]

For your first problem of pie chart, you can use the dimension as:

=if(ord(term_dt)=0,'Retained','Defected')

the ord function returns the ASCII value , and the term_dt without value has ord 0.

[#3]

For your second problem:

To count the total number of accounts as 5, you can use count(total account)) instead of hard coding it.

[#4]

thanks silky, some questions to follow up:

1. what is wrong with my logic for #1? the logic is actually the same i suppose, but ord works while isnull doesn't. why is this - shouldn't isnull work just fine here? i come from a database way of thinking about things and i have a difficult time understanding qlikview when concepts like this that i think i'm doing correctly do not work out.

2.
i.) for this one, total seems to be a special qlik keyword - how does it work? if i filter on accounts, will it only count the total of those that meet my filtered criteria? can i do something like total distinct account as well?
ii.) what about the null chunk for people that haven't terminated - i get a big bar at the end of leftovers, with no label on the x-axis. can i do something to hide this bar?

[#5]

Sure, i ll explain you about the Isnull and ord, why ord workd and isnull doesnt.

In the data i checked that where you have left the Date field blank , it has taken it as blank and not Null. and the ASCII value of Null is 78. But it is taking a character with ASCII value 0.

So, in case you want to use the IsNull logic, then you should give the value as Null to blank fields.

[#6]

For your null bar in the chart, you can hide by suppressing null values,

Right click on the Chart, go to chart Properties and open the Dimension tab, right below the dimensions , you ll see check box for Suppress when value is null. It will not show null values

[#7]

sily, back to my second question -

=count(id)/count(total id)

this worked well, until i brought in more data. let's say id is account_id and i have another table that has 1 to many addresses for the account. i really only care about the count versus distinct ids now. how do i do this?

=count(id)/count(total distinct id) didn't have any effect. thoughts?