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

# Last Reported Price in Pivoted Data

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.

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

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!

• ###### 1. Re: Last Reported Price in Pivoted Data

See attached.

I broke it into two calcs -- finding the last date for the ID in the month, and then finding that [basis] value.

It could all be collapsed into one calc, but I wanted to show the steps.

• ###### 2. Re: Last Reported Price in Pivoted Data

Thank you for your help. Unfortunately the Max(calculation1) is not returning the correct value. S1 January should be 0.50 not 0.68

• ###### 3. Re: Last Reported Price in Pivoted Data

S1 January should be 64 based on the data I see in Sheet 3.  And that's what I show on Supplier Summary.

• ###### 4. Re: Last Reported Price in Pivoted Data

I need the data to only pull from the report period that matches the report month. The values highlighted in yellow in the below screenshot are the values I want to be returned. Ex. Jan 2018 should be 0.50 for S1 since it is in the row for 1/1/2018 and the last reported column for Jan 2018

• ###### 5. Re: Last Reported Price in Pivoted Data

OK, I'm probably not understanding things here.

I changed my calcs to do MIN instead of MAX.  Look at Sheet 3.  The last reported MIN value in January for S1 is 53.

What additional factors do I need to look at to grab 50 from the January data for S1?

See attached.

• ###### 6. Re: Last Reported Price in Pivoted Data

You need to make sure that it is only pulling data from the correct report period row (column A) in the data. Yes 0.53 was reported on Jan 31st for S1, but that was in report period 2/1/2018.

When I pull in the filter for report period on the Supplier Summary sheet, Calculation 1 is blank:

I have been trying to figure out some sort of calculation along the lines of:

Last reported date:

if basis is not null then max report date,

else if basis is null then max report date -7

The result of that calculation would be 1/24/2018 for S1 and then I would create one last basis calculation to pull in the basis based on the last reported date.

• ###### 7. Re: Last Reported Price in Pivoted Data

Patricia Connolly wrote:

You need to make sure that it is only pulling data from the correct report period row (column A) in the data. Yes 0.53 was reported on Jan 31st for S1, but that was in report period 2/1/2018.

OK, so modify [Calculation1] to accommodate whatever rule your business has to decide which dates get included in a given month.  Looking at the other dates, I guess February 28 also goes to the next month, and maybe March 28, but what about April 25?  Actually, the logic for this would go into the [Max Date in month] calc.