4 Replies Latest reply on Nov 15, 2016 10:18 AM by Brisa Halviatti

    Dual Axis Line Graph; Two Date Fields Causing Issues

    Brisa Halviatti

      Context: I have two tables, one is the "work order" tables that has all the information about property maintenance's work orders. The second table is the Tenant/Unit table which has all the information about the resident living in the unit and the occupancy status of the unit.

       

      What I need: I want to create a dual axis line graph that shows the number of move outs that happened each month compared to the number of work orders that happened that same month. There is a "Unit Move Out" field in the Tenant data source, and "Call Date" For when a work order was scheduled. I created two calculated fields, Count of Work Orders is just "Count(Call Date)" and Count of move outs is similarly "Count(Unit Move Out)".

       

       

      At first I thought I had done this correctly, however when I compared the values I got when I used a dual axis as opposed to each graph separately I realized there was an issue.  If the date field I use is "Unit Move Out" date, then the count of work orders each month is drastically off. If i use "Call Date" from the work orders then the move outs are off.

       

      I think the problem here is coming from how I'm blending the data? Or perhaps the fact that I have two distinct date fields and need them to share a date field? I'm just not sure what the best course of action is. Any thoughts?

       

      I've attached two screenshots, so you can see how the data changes depending on which date field is being used. Which ever date field is used, the data from that same source is correct and the other is off.

        • 1. Re: Dual Axis Line Graph; Two Date Fields Causing Issues
          Sherzodbek Ibragimov

          Brisa,

          I looked your screen shots and yes you are right, you are comparing two different dates, and from a logical stand point call date and unit move out date may not be same (it is my guess). Generally, I would use dual axis to compare two different measures (Move outs and Work Orders) using one dimension (call date or move out date), but you can't do both dates as they are happening separate from each other. Alternatively, I would pivot all move outs, work orders and move out date and call date, then separate call outs and move outs as the measure from pivoted table and use pivoted date. If you could post your work, we can look at it and may be give more accurate reply than just a guess. Thanks

          Sherzod

          • 2. Re: Dual Axis Line Graph; Two Date Fields Causing Issues
            Ivan Young

            You are probably right in that the problem is coming from how you blend the data.  It's hard to offer too much guidance since I don't know how you have your data blended and what your data looks like.  The only thing I can think to try is create a custom date in Month / Year format in both your primary and secondary and then use that to blend.

            • 3. Re: Dual Axis Line Graph; Two Date Fields Causing Issues
              Sherzodbek Ibragimov

              Brisa,

              It has been almost two weeks since your question was answered, I just wanted to check back with you if solution provided here answered your question or not. If yes, please mark as Correct Answer or as Assumed Answer if you find your solution yourself so that it is cleared from question board that needs to be answered as well as other users in future can benefit from it. If not, please feel free to ask for help. I appreciate your help on that. Thank you.

              Sherzod

              • 4. Re: Dual Axis Line Graph; Two Date Fields Causing Issues
                Brisa Halviatti

                Unfortunately the solution wasn't quick or easy, but basically you were right; I created an "Event" field, either "Unit Move Out" or "Work Order Call" and had one "date" field for that row.

                 

                Thank you!