13 Replies Latest reply on Dec 19, 2018 4:01 PM by Jonathan Drummey

    Pre and Post event analysis when we are using blending options

    yamuna.kosireddi

      Hi Community,

       

      I am trying to achieve pre and post event analysis for selected event from the parameter.

       

      I have achieved this when data sets are in JOIN condition due to some data issues .Now we are connecting separately and using blending

       

       

      Below is the calculated field i have written and i was working fine if is in Join condition.Can any one tell me how do i change the formula:

       

      MONTH([Accident Date])={ FIXED MONTH([Accident Date]),YEAR([Accident Date]):max(if [Special Event Name1]=[Select Special Event] then MONTH([Accident Date]) END )}

      and

      YEAR([Accident Date])={ FIXED MONTH([Accident Date]),YEAR([Accident Date]):max(if [Special Event Name1]=[Select Special Event] then YEAR([Accident Date]) END )}

       

      Bolded one is the dimenion name from secondary data source..  how do i recalculated the formula

       

      Thanks

        • 1. Re: Pre and Post event analysis when we are using blending options
          Jim Dehner

          did you ever get an answer?

           

          all of the variables in an LOD expression need to come from a single data source -

          you were successful with a join why did you feel that you must go a blend?

           

          Jim

          • 2. Re: Pre and Post event analysis when we are using blending options
            yamuna.kosireddi

            Hi Jim,

             

            Reason for using blending is one of my data source is coming from tableau server.So it is not allowing to join two files.

             

             

            Any Help on this it would be grateful.

             

             

            Thanks

            • 3. Re: Pre and Post event analysis when we are using blending options
              Jim Dehner

              Please post your TWBX workbook and I will look at it

              Can't do much without seeing the data

              Thanks

              Jim

              • 4. Re: Pre and Post event analysis when we are using blending options
                yamuna.kosireddi

                HI Jim,

                 

                I have attached the sample workbook for your reference.

                https://drive.google.com/open?id=1ABNr_9FeUSwRo747qsKY8heKL-3y2T05

                 

                Workbook is created using joins.Now i want to get the same functionality using blending as there is a  limitation when we connecting from tableau server.So only portion is blending

                 

                if you provide me the solution that  would be very grateful

                 

                Thanks in advance

                • 5. Re: Pre and Post event analysis when we are using blending options
                  yamuna.kosireddi

                  HI community,

                   

                  Any Solution on above requirement.Require this on urgent bases.

                  Please help me out

                   

                  Thanks in advance

                   

                  Yamuna

                   

                  Jonathan DrummeyTableau User Group Las VegasTableau CommunityNational Community

                  • 6. Re: Pre and Post event analysis when we are using blending options
                    Jonathan Drummey

                    Hi,

                     

                    The workbook you posted is using a single data source. Without seeing a view using a data blend I’m afraid I can’t help you. Please post a packaged workbook with sample data using a data blend and the calculations so far and we can take a look.

                     

                    Jonathan

                    • 7. Re: Pre and Post event analysis when we are using blending options
                      yamuna.kosireddi

                      Thanks Jonathan,

                       

                      I have attached sample workbook with blending and also attached workbook with join.

                      With Join

                      https://drive.google.com/open?id=1ABNr_9FeUSwRo747qsKY8heKL-3y2T05

                      With Blending

                      https://drive.google.com/open?id=1Nig9v5WxKfQTZGz__2Db9f3nXFfP_Qx_

                       

                       

                      I am trying to achieve same functionality from join to blending.

                       

                      If you help me ,it will solve my problem.Jonathan Drummey

                       

                      Thanks

                      • 8. Re: Pre and Post event analysis when we are using blending options
                        Jonathan Drummey

                        Hi,

                         

                        Thanks for posting the workbooks, I can see them.

                         

                        I’ve got a few questions before proceeding further:

                         

                        1) In looking at the calculation in the single-source workbook it appears that the goal is (for the selected event) to compare the count of incidents for event dates in the month that the event occurs vs. the count of incidents for non-event dates in the month. Is that correct?

                         

                        2) Given #1 I can see from the blended source workbook that events have start and end dates, and in the case of the 77th All India Industrial Exhibition the date crosses a month boundary. So it would seem to me that if you’re going to look at event days then the “join” between the events and the accident data should be on every date of the duration between start & end, not just the single start date, so all days can be properly counted. Am I mistaken in that or do you still want to “join” on a single day?

                         

                        3) On a related note since the Avg of Accidents calculation is using COUNTD() if there are no accidents on a given date then there will be no data and therefore that the average will be the “average accidents per day for days when there were accidents” as opposed to the “average accidents per day in the month”. I’m guessing that you’d want the latter, if so can you guarantee that there will always be at least one accident on every day of the month? For example in the provided data for September 2017 there were accidents on 29 days of the month.

                         

                        Jonathan

                        • 9. Re: Pre and Post event analysis when we are using blending options
                          yamuna.kosireddi

                          Hi,

                           

                          1) In looking at the calculation in the single-source workbook it appears that the goal is (for the selected event) to compare the count of incidents for event dates in the month that the event occurs vs. the count of incidents for non-event dates in the month. Is that correct?

                          Yes,i am trying to show event occurs vs. the count of incidents for non-event dates in the month.

                           

                          2) Given #1 I can see from the blended source workbook that events have start and end dates, and in the case of the 77th All India Industrial Exhibition the date crosses a month boundary. So it would seem to me that if you’re going to look at event days then the “join” between the events and the accident data should be on every date of the duration between start & end, not just the single start date, so all days can be properly counted. Am I mistaken in that or do you still want to “join” on a single day?

                          Yes, i think you are correct that better to count the incidents between start date and end date of the event

                           

                           

                           

                          3) On a related note since the Avg of Accidents calculation is using COUNTD() if there are no accidents on a given date then there will be no data and therefore that the average will be the “average accidents per day for days when there were accidents” as opposed to the “average accidents per day in the month”. I’m guessing that you’d want the latter, if so can you guarantee that there will always be at least one accident on every day of the month? For example in the provided data for September 2017 there were accidents on 29 days of the month.

                          I considered that there is at least one accident on every day of the month.

                           

                           

                          Thanks

                          • 10. Re: Pre and Post event analysis when we are using blending options
                            yamuna.kosireddi

                            Hi Jonathan,

                             

                            For point 2,

                             

                            What we are doing is that we are repeating the number of rows based on the start date and end date.

                            Ex:

                            If Event "Pongal" is happend between 01-01-2018 to 03-01-2018.Then we are storing same event three times

                             

                            Thanks

                            1 of 1 people found this helpful
                            • 11. Re: Pre and Post event analysis when we are using blending options
                              Jonathan Drummey

                              I've attached a 10.5 workbook using data blending.

                               

                              There are three challenges here:

                               

                              1) The event name is coming from the blended source but the goal is to end up with a division of data each month into "non-event days" vs. the event name.

                               

                              To solve this I simply right-clicked on the Null event name created by the data blend, chose Edit Alias... and then entered the alias.

                               

                              2) The Special Event Analysis sheet needs to filter for only the event days.

                               

                              In the Events source I created a Selected Event Date calculated field with the formula  DATE(IF [Special Event Name] = [Select Special Event] THEN [Date] END) and then added that to the Filters Shelf, and exclude the Null values added by the data blend that represent all the other dates in the accidents source.

                               

                              3) Even when an event is on a single day in the Events Analysis sheet the goal is to filter for that entire event month so the view can compare the daily incident rate for event days vs. non-event days in that month. This is more challenging and I considered a few options:

                               

                              - Regular aggregates e.g. MIN() and MAX(). This wouldn't work in this situation because the vizLOD for the Event Analysis sheet is at the grain of Month & Event, and the presence of the Event dimension makes the grain too fine for the aggregate to work.

                               

                              - Using an LOD expression. This is not possible in this scenario because we'd want the LOD results to use the event date from the events source to populate across rows in the accidents source, and at this time LODs are only computed in a single Tableau data source. (You might see resources online where people say that LOD expressions can't work in a data blend, that statement is *only*  true for this particular case when the LOD is trying to work across the sources as in this case. We *can* use LODs in each source in a data blend subject to some limitations, those limitations are the same ones that get in the way of using COUNTD(), MEDIAN(), or PERCENTILE() in blended views).

                               

                              - Using table calculations. This could definitely work but it would create more load on the system as Tableau would be returning a larger set of query results. And since I don't know the actual size of your data I skipped that one.

                               

                              - Using a duplicate data source, blending at the month(s) of accident & month(s) of the selected event, and then excluding the Null event dates added by the blend (so we only keep months where there's an event). We can't have blends working at different levels in this same view, however if we duplicate data sources we can have multiple blends working at different levels. I went with this route because even though it adds another blend it's really simple to set up and works at a dimension level inside the blend so the query results are kept to the smallest possible results.

                               

                              For the duplicate data source solution I did the following:

                               

                              1. In the accidents source created an Accident Month field.

                              2. Duplicated the events source.

                              3. In the events source created a Selected Event Month calculated field with the formula DATE(IF [Special Event Name] = [Select Special Event] THEN DATETRUNC('month', [Date]) END).

                              4. Edited the data relationships to relate Accident Month to Selected Event Month.

                              5. In the Events Analysis sheet turned on the data blend for Selected Event Month and added that to the Filters Shelf, and excluded the Null values added by the data blend that represent all the other dates in the accidents source.

                               

                               

                              I also made a couple of other changes:

                               

                              1) The DateB calculation is inefficient since it's doing a boolean evaluation to return True/False as strings, we can just use a boolean evaluation. The Date jtd calculation does that. Also I changed from using Accident DateTime to Accident Date. The reason why is that that generally when filtering at the top end of the filter if we filter for 1 December 2018 then we'd expect to see results for 1 December 2018 however when using accident datetimes any accident datetime after 1 December 2018 at midnight would be filtered out since the parameter is returning 1 December 2018 at midnight.

                               

                              Note that a further efficiency would be to use a range filter control on the accident date directly instead of two parameters.

                               

                              2) I added an "Is Event Day" calculation to the accident data source and added that to Color for the Events Analysis sheet. That wasn't particularly necessary, however I did that to integrate better with the Day wise Accident Analysis tooltip sheet where I added a dual axis shape mark colored with Is Event Day (and synchronized the axes and hid the secondary axis) so we could easily see where the event days were in the month.

                               

                              3) Deleted extraneous calculations & worksheets.

                               

                              Let me know if you have any questions!

                               

                              Jonathan

                              2 of 2 people found this helpful
                              • 12. Re: Pre and Post event analysis when we are using blending options
                                yamuna.kosireddi

                                Thanks for your solution and suggestions Jonathan

                                but my bad luck "EDIT ALIAS"  option not there when connect the data source from tableau server.

                                 

                                Thanks

                                Yamuna

                                • 13. Re: Pre and Post event analysis when we are using blending options
                                  Jonathan Drummey

                                  Hi Yamuna,


                                  I just saw this thread, here's what you can do. I was using an Alias as a shortcut to avoid having to create another calculated field, I should have remembered that we don't have that capability with published data sources.

                                   

                                  I created a calculated field with the formula IFNULL(ATTR([Special Event Data For Tableau (Special Event Calender Data_Tableau_07Feb18V4)].[Special Event Name]), 'Non-event Days') and added that to the view as another pill on Columns while turning off Show Header for the Special Event Days dimension coming from the secondary:

                                   

                                  Screen Shot 2018-12-19 at 7.01.01 PM.png

                                   

                                  v10.5 workbook is attached.

                                  1 of 1 people found this helpful