7 Replies Latest reply on Jul 24, 2018 8:56 AM by Joe Oppelt

    Last Reported Price in Pivoted Data

    Patricia Connolly

      I need to pull in the last reported price for each supplier for the reported month, as seen in the Supplier Summary sheet. This data has been pivoted from the original source so that I can have all the basis prices in one column and the report date in another.

       

      Currently I am using the MIN function to pull the basis price for each reported month. The issue I am running into is that some of the suppliers do not report in the “last reported date” of the month. As you can see in my data below, when I filter the report date to January 31st, I have nulls for S1 and S2 because they did not report on Jan 31st. But if I add in all of the report dates for the month, it would not be giving me the correct price, because the next issue is that the last reported price is not necessarily the MIN.

       

      Ex. S1 reported $0.48 on January 10th, and $0.50 on January 24th. I need $0.50 to be displayed, but with the MIN function and all dates for January selected, it will display $0.48.

       

      tab2.JPG

       

       

      Looking at the raw data, the calculation I need to create in tableau is something along the lines of:

       

      For each supplier, and each month, find the max reported date that has a price reported and return that value (highlighted in yellow below):

       

      Supplier A:

      January- pull from 1/31, February- pull from 2/28, March- pull from 3/28

       

      Supplier S:

      January- pull from 1/24, February- pull from 2/21, March- pull from 3/28

       

      Tab 3.JPG

      I was trying to figure out some sort of IF or CASE statement that would find the max date in the month and if not null pull the price, and if null it would move onto the next maximum reported date.

       

      Any insight on how I could accomplish this in tableau would be much appreciated!