Pivot Behaving Badly

[callummcgrath]

Hey,

I've got a pivot that shows the revenue from a company in the last year they placed orders, (with the aim of finding companies who placed large orders in 2015/16 but who haven't placed orders in 2017). However, my pivot doesn't formulate for all companies and leaves some as 0, even though there are loads of orders in the raw data it is pulling from. Due to confidentiality I can't send the doc over, so hopefully someone may spot something from the following screenshots.

The first is the pivot, and the values it is returning.
Pivot errors.PNG

The second, shows the formula I have in calculated fields.
calculated field.PNG
*Test Data Q = Price to Client, Test Data AE = year of jobs, f7 = year of latest job, =Test Data C = company name, a7 = company name

Lastly, I have re-written the formula outside the pivot so you can see that there is data there, but it's the pivot that can't find it (assuming something wrong with my calculated field)
other formula.PNG

Thank you!

[Keebellah]

Have you 'spanked' the pivot for behaving badly?

Are you sure that the pivot covers the entrie data range? If you expand a range you have to take care that the range you selected previously also refreshes

[callummcgrath]

At first I thought that was a technical term that I wasn't aware of!
Yes, I have checked, it 100% covers all data, and refreshed.

I've taken names out etc. and attached an example file now!

Thanks

[xlnitwit]

Hi,

You'll need to do the calculation in the source data, with a simple IF formula. Your calculated field will only work where every year value for a company is equal to the latest year value for that company.

[callummcgrath]

Ah I see, I'll give that a go and get back to you. Thanks!

[callummcgrath]

That did the job, thank you xlnitwit!

[xlnitwit]

You're welcome. Thank you for the rep.