6 Replies Latest reply: Jan 28, 2010 3:39 PM by mikedev10 RSS

    newbie data presentation

    mikedev10

      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?

        • newbie data presentation
          silky.agarwal

          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.

            • newbie data presentation
              silky.agarwal

              For your second problem:

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

                • newbie data presentation
                  mikedev10

                  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?

                    • newbie data presentation
                      silky.agarwal

                      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.

                       

                      • newbie data presentation
                        silky.agarwal

                        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

                          • newbie data presentation
                            mikedev10

                            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?