2 Replies Latest reply: Apr 17, 2013 3:02 AM by geraldpeters RSS

    exclude records from a chart based on value

      I am trying to create a bar chart looking at planned start and actual start dates for projects. The dates are entered as quarters eg Q1, Q2 etc

       

      planned start is a dimension

       

      actual start is entered as an expression

       

      I want a count of planned starts and actual starts on the chart - how do I restrict the count to exclude blank fields for the actual start (ie the project was planned to start in Q1 but has not currently started)

       

      I did try adding both fileds as dimensions, hoping that I could exlude nulls, but the actual and planned fields were stacked in the bar chart. The only way I could get them displayed along side each other was to add planned as a dimension and actual as an expression.

       

      regards

       

      Gerald

       

      Message was edited by: geraldpeters - file added as suggested

        • Re: exclude records from a chart based on value
          Kaushik Solanki

          Hi,

           

               It will be helpful if you can post QVW file with sample data.

           

          Regards,

          Kaushik Solanki

          • Re: exclude records from a chart based on value

            thanks for that - it is ceratinly closer than I got. What I was looking for was a count of projects that started per quarter compared to the count of projects that were planned to start per quarter. So for Q1, Q2, Q3 and Q4 I would expect to see a planned bar (which seems fine beacuse all records have an entry of Q1 -4 or TBC) and an actual bar which should only currently be populated with Q1 or NS (for Not started). I did originally leave this value empty in my table but the empty fields were also being counted

             

            I would expect to see a count of 20 for planned start in Q1 and a count of 13 for actual start (ie there are 13 records with an entry of Q1 in the actual start field). The QVW you attached shows 14, values for Q2 onwards should have a count of 0 (ie no projects have started because Q2 + represents the future)

            I've attached a spreadsheet with the raw data - note that I am happy to replace the NS (not started) with an empty field or a zero if it helps

             

            thanks again for your help.

             

            Gerald