0 Replies Latest reply on Nov 12, 2018 11:33 PM 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, but naturally because of the way I have performed my calculation the data is split into blocks and I am unable to draw a continuous line graph:

       

      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 confidential.

       

      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 changes every 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.

       

      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.