4 Replies Latest reply: Dec 17, 2014 7:43 AM by Mostafa Zakaria RSS

    How does one juxtapose 2 heights in one Bar Graph?

    Mostafa Zakaria

      Hi,

       

      I have a situation where we have two calculated fields counting the total number of days, one for "actual number of days" a job was accomplished, and a second for "recorded number of days". I'm trying to juxtapose these two columns, onto a single vertical bar chart. Seems pretty simple, yes?

       

      However, this is not possible on any of the bar chart or needle graph visualizations, since they all accept one height. Moreover, grouping is not an option since these fields are two separate columns in each row.

       

      Is there some way I can define a "universal" Number of days column with a fixed range, and plot the heights of the "actual" vs. "recorded" number of days side-by-side on the same visualization?

       

      Best Regards

       

      M.Zakaria

        • Re: How does one juxtapose 2 heights in one Bar Graph?
          Olly Bond

          Hello Mostafa, and welcome,

           

          This is possible using Datawatch Modeler - a bit fiddly if you're working from a spreadsheet or database, as the Multi Column Region option isn't open to you there, but even then it's straightforward in two projects. The first project should open the data, and export the filtered table to a fixed width text report. The second project should open this report, and define a MultiColumn Region with two columns. You'll then get twice as many rows in your table as your were expecting, and can define a simple calculated field

           

          if (Column()=1 ; "Actual" ; "Recorded")

           

          to give you the extra dimension you need to visualise. This technique is covered in the Invisible Data webinar here: http://www.datawatch.com/video/invisible-data-advanced-techniques-for-datawatch-modeler/

           

          I see that you work for Datawatch? If you could ask your colleagues in Product Development to consider enabling the Column() function to be applied to database sources as well as to report files, I'd be very grateful. This would save you from having to use two projects to handle this data and would let you accomplish it one.

           

          Best wishes,

           

          Olly

          • Re: How does one juxtapose 2 heights in one Bar Graph?
            Joseph Territo

            Mostafa,

             

            I am no expert in Designer yet, but from what I gather and what I would do is to have a separate column, let's call it "Type", and in this column it would state either "Actual" or "Recorded" based on the Number of Days value. In a separate column, you would just have a value for the Number of days whether it be of Type "Actual" or "Recorded". I realize this would have to make you create two rows for each set vs one single row of data with two columns.

             

            Designer would then separate these in a bar chart by putting the "Type" field as a Breakdown and also in Color, and then the Number of Days values as the Height. Then I would create a fixed range on the Height to whatever your fixed range would be.  If there is another method that can accomplish the same thing with your set up without doing this I don't know what it is.

             

            I hope this helps.

            • Re: How does one juxtapose 2 heights in one Bar Graph?
              Theo Klemming

              Hi Mostafa

               

              Joseph is right. And you should be able to achieve the re-shaping needed by using an unpivot transformation in Designer. Let's assume you have a data set like this:

               

              ProjectIDRecordedDaysActualDays
              P147
              P263
              P35

              6

               

              What you want to do then is apply an unpivot transformation, and include both the numeric columns.

              In your bar chart, add ProjectID, then Measure to the Breakdown. Color by the Measure column. Go into settings of the bar chart and change to Stacked.

              • Re: How does one juxtapose 2 heights in one Bar Graph?
                Mostafa Zakaria

                Hey Guys,

                 

                First off, I just wanted to thank you all for your inputs. I finally got it to work, and the solution Joseph Territo and Theo suggested worked like a charm. I had to apply a function onto the resulting value to clean it up (the state of the data is less than ideal and there were some massive outliers resulting from NaN conflicts).

                 

                But all in all, the unpivot transformation worked.

                 

                Very much obliged!