1 2 Previous Next 22 Replies Latest reply on Jul 30, 2018 6:42 AM by Kris Smith

    combine two date fields on same axis

    Allison Suarez

      I have two field, open date and closed date.  I am plotting them as Month/Year.  I can get it to show as two charts but am unable to combine them into one.  I tried pulling the axis from one to the other but that just overwrites the data.  Is there a way to combine these two on the same chart?

      Thanks!

        • 1. Re: combine two date fields on same axis
          Allison Suarez

          Also, I am using the same field (instance ID) as a count for my measure.  I don't know if that makes a difference.

          • 2. Re: combine two date fields on same axis
            P Fro

            Two dates on the same axis? Wouldn't you rather put one on a dual axis. That way you could keep your measure on one graph, but be represented by two different dates.

            • 3. Re: combine two date fields on same axis
              Allison Suarez

              Well I'm trying to do an area chart with the number opened and number closed for each month.

              • 4. Re: combine two date fields on same axis
                P Fro

                Sorry I'm not sure how you could accomplish that with an area chart. Without using a different type of visualization, the only way I can think of to add two dates is by running a synchronized dual axis with your instance ID setup as discrete. That would show you a mark (like a circle) on the date an item opened and another on the date it closed.

                • 5. Re: combine two date fields on same axis
                  Alex Wang

                  I am having the same problem. I am trying to calculate a cancellation rate, and the equation is as follows:

                  [Total Amount of Cancellations in a particular month] / [Total Amount of Purchases in the same month]

                   

                  My data has both a Cancel Date and a Purchase Date... and I need to overlay both on the same axis essentially. Any help from others?

                  • 6. Re: combine two date fields on same axis
                    Tracy Rodgers

                    Hi Alex and Allison,

                     

                    One option might be to create a dual axis with the two dates (using Continuous month) and adding the third measure to the rows shelf.

                     

                     

                    Hope this helps!

                     

                    -Tracy

                    3 of 3 people found this helpful
                    • 7. Re: combine two date fields on same axis
                      Alex Wang

                      Yes Tracy! I didn't know about the dual axis function....

                       

                      A follow-up question though: Say in your graph, I wanted to calculate the total $ of items shipped / total $ of items ordered within each month... is there anyway to do that and present that as one singular line graph?

                      • 8. Re: combine two date fields on same axis
                        Tracy Rodgers

                        Sure, you would just create a calculated field similar to sum(Sales)/total(sum(Sales)) and place it on the rows shelf.

                         

                        -Tracy

                        • 9. Re: combine two date fields on same axis
                          Alex Wang

                          Hi Tracy!~

                           

                          I tried that but unfortunately, I think the Total() only works across rows so it is summing all the month's sales sums for each particular metric. So all of my month's %'s equal 100% for shipped.. and then 100% for ordered.

                           

                          However, I was hoping to calculate within each month the %... so the total $ of items shipped / total $ of items ordered within each month.

                           

                          I've added screenshots of what I have. I'm ending up with 2 lines that represent the % of total of each metric by month. What I'm expecting is just one line that calculates one % based off both metrics for each month.

                           

                          Hopefully that makes sense? Thanks so much for your help by the way - I truly appreciate it!7-30-2013 5-07-08 PM.jpg

                           

                          7-30-2013 5-08-14 PM.jpg

                          • 10. Re: Re: combine two date fields on same axis
                            Rachel Alonso

                            I recently achieved something similar by adding a new data connection using a UNION ALL custom sql statement. I found the attached public workbook and looked at the calculated fields and custom sql to achieve the same result with my own data source. It sounds like you are trying to do something similar.

                             

                            Before, our data was as follows:

                            Instance ID #       Date Entered          Date Closed

                            ID-1                    1/1/2013               2/15/2013

                            ID-2                    2/1/2013               3/15/2013

                            ID-3                    3/1/2013               4/15/2013

                             

                            To have both dates on one graph (we've done both bar charts and line graphs), the UNION ALL statement has the data like this now:

                            Instance ID #         Event Date            Event Type

                            ID-1                    1/1/2013               Entered

                            ID-1                    2/15/2013             Closed

                            ID-2                    2/1/2013               Entered              

                            ID-2                    3/15/2013             Closed

                            ID-3                    3/1/2013               Entered                 

                            ID-3                    4/15/2013             Closed

                             

                            I made a variety of calculated fields to show the count entered, the count closed, and the difference between the two. Then I use MONTH of event date to show what happened in each month.

                             

                            Hope this helps!

                            6 of 6 people found this helpful
                            • 11. Re: combine two date fields on same axis
                              Matt Lutton

                              Alex:

                               

                              It sounds like you just need to set the Table Calculation to Compute Using a different setting.  Right click on your Overall CxR Table Calculation, choose Compute Using, and you can change the way it is computing.

                              Table Calc ComputeUsing Demo.png

                              • 12. Re: Re: Re: combine two date fields on same axis
                                Alex Wang

                                Thank you SO much Rachel!

                                 

                                Your workbook example worked perfectly and I was able to reshape my data so I could get the view I was looking for! You rock! Do I get to +1 your response somehow if I'm not the original thread author?

                                 

                                I ran into another problem with not being able to put 2 measures as lines and 2 other measures as bars on the same graph but it seems that this is not possible from all the research I've done on the forums. Bummer.

                                • 13. Re: combine two date fields on same axis
                                  S D

                                  Useful info.

                                  SD

                                  • 14. Re: combine two date fields on same axis
                                    gagandeep.singh.3

                                    exactly the same issue..thanks for the above guidance!!

                                    1 2 Previous Next