13 Replies Latest reply on Apr 20, 2016 8:53 AM by Alexander Bradley

    Trouble with multiple columns of dates

    Alexander Bradley

      Attached is a file similar to the one I am working on. There are 10 types of animals. Along with those animals, there are dates associated with estimated birth dates. I have written a few calculations that say if the date is > 0, put "A", and so on for each column for charting purposes.

       

      I am running into a problem with having 8 different dates in 8 different columns. If I put "Type" in the row, "A_Date" into the column and then enter "A_Date" into "label" it works and shows the correct verbiage for the associated months. However, when I want to enter "B_Date" into the same chart it does not work because the A_Date is dominant.

       

      I do not know how to have the "B_Date" show up in the correct associated date. Any ideas?

      Please let me know if I did not explain well enough.

        • 1. Re: Trouble with multiple columns of dates
          Joe Oppelt

          I'm not sure I understand your issue, but here's a stab.

           

          Create a parameter:  [Which Date?]

           

          This is how the user will decide which date he wants to see.

           

          Create a calc.

           

          Case [Which Date?]

          when "A Date" then [A Date Conversion]

          when "B Date" then [B Date Conversion]

          ...

           

          Also create a calc:

           

          Case [Which Date?]

          When "A Date" then [A Date]

          when "B Date" then [B Date]

          ...

           

          Put the date calc on your Columns shelf and the text calc on the label shelf.  Then, as the user switches date selections, the sheet will automatically update accordingly.

          • 2. Re: Trouble with multiple columns of dates
            Alexander Bradley

            Joe,

            Thanks for the response on such a vague description. Instead of the user selecting separate dates, I would like all of the dates to show on one table. I have added an attachment of the view that I would like (I created it in a way that isn't possible currently and only to resemble the desired look). Also included, is the incorrect view from before. Please let me know if this helps at all.

            • 3. Re: Trouble with multiple columns of dates
              Joe Oppelt

              Is this close to what you are looking for?  (I didn't do it for "D" and "E".  Just A, B, C, and F.)

              • 4. Re: Trouble with multiple columns of dates
                Alexander Bradley

                Joe,

                The "correct" tab is how I would like it to look. The only way I am able to get it to look this way is through the "Manipulated Correct" data source tab. If you are able to create the same look on the "Incorrect" tab only using the "Current Problem" data source tab that would solve my problem. The only reason I have the 'Correct' tab and 'Manipulated correct' is a viewpoint for someone trying to solve this issue and NOT to be used to solve the solve this problem.

                I am sorry for the confusion, I am having trouble clearly stating my problem. I hope I have clarified myself now.

                • 5. Re: Trouble with multiple columns of dates
                  Joe Oppelt

                  I need to understand what makes the data in the "Correct" tab correct.  For instance you only have "A" in April cell for Birds.  But as best as I can tell, in the data you have provided in your original xls file, "Birds" has data for multiple Date columns.

                   

                  "Rabbits" has A and B in May of 2014 in your Correct tab.  I see more than just A and B in the excel file you provided.

                   

                  Basically what I've done in my example is to create a calc that decides whether there should be an A for any category, and another for B, etc.  Then just concatenate all of them together and spit out the concat calc.  Whatever logic determines whether an A or a B, etc., should appear can be added to the individual calcs.

                  • 6. Re: Trouble with multiple columns of dates
                    Alexander Bradley

                    The data in the correct tab, is correct based off of the manipulated correct tab which is where I went in and made the data a specific was to show how I am trying to get it to look and will not be part of the file otherwise.

                    Now you mention the differences in columns, I am looking for a way to be able to see all of those dates from each column on one chart through the "A" "B" "C" "D" conversions. So if the "A Date" has cats in May of 2013, Show "A" in the cats row for May. If Dogs has a "D Date" in July of 2016, Show "D" in the Dogs row for July. If both Squirrels and Toads have "A Date" and "B Date" in May of 2014, show "A" and "B" in May for both the Squirrel and Toad row's. And so on..

                    Again, I apologize for the confusion.

                    • 7. Re: Trouble with multiple columns of dates
                      Joe Oppelt

                      Unless I'm horribly mistaken, what I showed in my sheet IS what you described.  In the original data you provided (not the manipulated data), most of the [Type]s have all date columns except Bulls, Cows, Goats and Rams, which only have "F".  (Those 4 also have "G", but I didn't do all the date columns.)

                      • 8. Re: Trouble with multiple columns of dates
                        Alexander Bradley

                        While your calculations help and give me a better understanding, I see you are using the date field from the "correct problem" data tab which is why it works, because the dates come from that one column. But when there are multiple columns of dates is where I see the problems. The only data that I will have to work with is from the "Current Problem" data series and not the "Manipulated Data" which is where you are pulling the Date from. Try and take the "Correct Problem" out of the source area and replicate the chart by only using the "Current Problem." Hopefully you are able to come up with something! Because I know I have been struggling.

                        • 9. Re: Trouble with multiple columns of dates
                          Joe Oppelt

                          I'm using the data source you provided in the workbook.  I don't have any other data source to use.

                          • 10. Re: Trouble with multiple columns of dates
                            Joe Oppelt

                            Oh, maybe I see what you are saying here...

                             

                            If I remove all the "Manipulated Correct" fields from the dimensions, I won't have a date axis to run the data against.  You want a uniform date column to run this along.  Month/Year.  Something like that.  Then, whenever any of the "A Date" or "B Date", etc., land in one of those month/year axis points, it gets reported.

                            • 11. Re: Trouble with multiple columns of dates
                              Alexander Bradley

                              Yeah, you got it! That is what I am struggling with. Being able to report them all correctly using the dates column.

                              • 12. Re: Trouble with multiple columns of dates
                                Joe Oppelt

                                You're struggling with that because your data as it is shaped now won't let you do what you want to do.  It never will.

                                 

                                I reshaped your data so that you will have one date axis, but you can still create your own calcs to break this into A_Date, B_Date, etc.  See attached excel file.

                                • 13. Re: Trouble with multiple columns of dates
                                  Alexander Bradley

                                  Thank you for your help! I appreciate it!