5 Replies Latest reply on Nov 5, 2018 8:29 AM by Tiago-Mateus Korynek

    How do I create a Line Graph for data controlled via a Date Parameter?

    Tiago-Mateus Korynek

      Hi all,

       

      I have a calculated field called CollatedIndex which is calculated from 2 data sources and is calculated as follows:

       

      ([Sheet1 (WO - 12 Months)].[P&Ms Percent Rank]*1)+([Ix Percent Rank]*1.5)

       

      P&Ms Percent Rank is calculated via:

       

      IF (SUM([P&Ms])/WINDOW_MAX(SUM([P&Ms]))>0) THEN (SUM([P&Ms])/WINDOW_MAX(SUM([P&Ms]))) ELSE 0 END

       

      Ix Percent Rank is calculated via:

       

      IF (SUM([Ix])/WINDOW_MAX(SUM([Ix]))>0) THEN (SUM([Ix])/WINDOW_MAX(SUM([Ix]))) ELSE 0 END

       

      SUM([Ix]) is calculated dynamically when using the parameter which changes ReportingPeriodStart and ReportingPeriodEnd:

       

      IF (IF [Type]="GOOD" AND [Date] >= [ReportingPeriodStart] AND [Date] <= [ReportingPeriodEnd] THEN [Ix] END)>0 THEN (IF [Type]="GOOD" AND [Date] >= [ReportingPeriodStart] AND [Date] <= [ReportingPeriodEnd] THEN [Ix] END)

      ELSE 0 END

       

      Likewise SUM([P&Ms]) is calculated dynamically when using the parameter which changes ReportingPeriodStart and ReportingPeriodEnd:

       

      IF (IF DATETRUNC('month', [Issue Date]) >= DATETRUNC('month', [ReportingPeriodStart])  AND DATETRUNC('month', [Issue Date]) <= DATETRUNC('month', [ReportingPeriodEnd]) THEN 1 END)>0 THEN (IF DATETRUNC('month', [Issue Date]) >= DATETRUNC('month', [ReportingPeriodStart])  AND DATETRUNC('month', [Issue Date]) <= DATETRUNC('month', [ReportingPeriodEnd]) THEN 1 END)

      ELSE 0 END

       

      How do I make a line graph so that I can plot the last 12 months of the field CollatedIndex which changes dynamically when I change my parameter? I seem to be running into issues as I need each month's CollatedIndex to be recalculated using the previous 12 month period's data.

      By duplicating ReportingPeriodStart and ReportingPeriodEnd to make ReportingPeriodStart1monthago, ReportingPeriodStart2monthsago etc etc I have managed to make the following:

       

      However I cannot turn it into a line graph as I have essentially created 12 individual calculated fields which each individually calculate their own CollatedIndex based on their respective 12 month reporting periods.

       

      Unfortunately I cannot share the raw data as it is confidental.

        • 1. Re: How do I create a Line Graph for data controlled via a Date Parameter?
          Joris Platenburg

          Hi Tiago-Mateus,

           

          Looks like you've got an interesting challenge on your hands!

           

          Do I understand correctly that the parameter you are changing should in fact be relative to the month of the relevant data?

          It sounds very much like you would need to use a moving calculation instead of trying to achieve your goal using duplicates of the [ReportingPeriodStart] and [ReportingPeriodEnd] parameters. In the moving calculation you could then use different parameters to indicate the timespan over which you calculate your values. I.e. you could introduce parameters [LeadingMonths] and [TrailingMonths] to calculate the respective Percent Rank values for a given month.

           

          Given the complexity of your calculations, would it be possible to break things down into a more simple example and try to take it from there? With some dummy data this would also solve the confidentiality issue and we'd have the possibility to share workbooks.

           

          Looking forward to your input!

          • 2. Re: How do I create a Line Graph for data controlled via a Date Parameter?
            Tiago-Mateus Korynek

            Hi Joris,

             

            Yes it definitely is an interesting challenge getting my head around the actual mathematical calculation that I am wanting to replicate in Tableau. A moving calculation is definitely the type of calculation that I will be wanting to perform. My goal is to have 1 parameter which I change which will then changesevery other calculation from this point. My parameter would be choosing the month and year that my report would be generated for. This would be in the format January, 2018 or February, 2018 etc.

             

            From setting this parameter, I would then want Tableau to calculate the Reporting Period Dates which using January, 2018 selection as an example would be:

             

            Reporting Period End: Last Day of the month which in this case would be: 31st January, 2018

            Reporting Period Start: First Day of the month 12 months ago which in this case would be: 01st February, 2017

            The reporting period above would give me the most current up to date CollatedIndex calculation for the report generated on January, 2018.

             

            I would then want the parameter selection of January, 2018 to automatically give me last month's Collated Index which would calculate from the following period:

             

            1MonthAgo Reporting Period End: Last Day of the month from 1 month ago which in this case would be: 31st December, 2017

            1MonthAgo Reporting Period Start: First Day of the month from 13 months ago which in this case would be: 01st January, 2017

            This reporting period above would give me a different value of CollatedIndex based on a different span of data.

             

            This would then repeat for a full set of 12 months to track.

             

            I will work on a set of dummy data to work with, but hopefully this gives you an idea of what I would like to create. I will attached the dummy data and workbook in a bit.

             

            • 3. Re: How do I create a Line Graph for data controlled via a Date Parameter?
              Joris Platenburg

              Hi Tiago-Mateus,

               

              I've just created a tiny workbook, please find it attached to this post.

              This workbook illustrates the idea I wanted to bring across: you can create a moving calculation where the period over which your calculation is performed is defined based on a parameter.

              The second sheet of the attached workbook allows you to change the parameter which increases or decreases the number of months that are taken into account for the moving calculation I created. In doing so, I have created a line graph similar to what I believe you would be interested in.

              In my example the moving calculation is a simple sum and everything about is extremely simplistic, but it might just put you on the right path.

               

              Looking forward to hear from you whether this helps!

              • 4. Re: How do I create a Line Graph for data controlled via a Date Parameter?
                Tiago-Mateus Korynek

                Hi Joris,

                 

                Apologies for the delay in getting the desensitised workbook over to you. The data I have in this workbook is completely fictional and I have modified every column.

                 

                The way I have the crosstab set up, I can select the month of my reporting period by using the Bi-Monthly Month parameter:

                This sets the data calculation period for the crosstab to calculate from 1st September 2017 to 31st August 2018.

                 

                Is there a way I could create a visualisation like the below using the data that I have in my workbook and have it so that I show a running sum of the delays > 15 minutes for the No.1 ATA? The No.1 status would be given by the Overall Ranking value:

                And the running sum would be a running 12 month period of the data calculation period.

                 

                 

                Let me know if I need to explain exactly how I want the calculations to function.