Pivot Table - just certain subtotals

[sabsx]

Hi!

I can't find a way to display just subtotals which are > 500k... can anyone give me a hint?

regards,

sabsx

 

[denismccarthy]

Without seeing the data, you could try using a formula in the pivot to filter out the info you want.

 

[sabsx]

how can I display data with a formula? I just know calculated fields...

My data are quotes...

so I have quote number, item number, item name, price, pieces, total... and my subtotals are the totals of each quote... and I just want to display quotes which are priced 500 or more...

 

[Fazza]

Just a hint ...

Just a hint: one way, use SQL to limit your data set before the pivot table.

 

[sabsx]

unfortunately I have no knowledge of sql... how can I limit my data with sql in order to get 2 pivots for quotes with subtotals over 500 and less than 500?

 

[Fazza]

Incorporating SQL to limit your data set to results either more or less than 500k is a powerful approach. It is sophisticated and would take a little effort to explain. However, a simpler alternative way might also do the job.

Create one pivot table with the subtotals of interest. Then add a new column to your source data "Which One". Populate it via a GETPIVOTDATA function that checks the relevant subtotal for each record. Such as =IF(GETPIVOTDATA(the subtotal for the record)>500000,"Big Ones","Little Ones")

Then run your final pivot table from this larger data set (that is with the new column) and make the "Which One" field a page field. Via this page field, select either the "Big Ones" or "Little Ones", or even both/all.

You might have to refresh the first helper pivot table and then refresh the final pivot table separately to have everything work correctly. I haven't tested this, BTW.

HTH, Fazza

 

[sabsx]

Just tried this... unfortunately doesn't work... think the problem is that I have duplicate quote numbers - if a quote contains more than one item I have the quote number several times...

I thought SQL just works with Access?

 

[Fazza]

I just tried it and it works for me. It is simple enough.

Can you please try again and post an image of what you're doing if it doesn't work? Or spell out what doesn't work, which step isn't doing what it should.

There must be something simple that is not being done.

(SQL is for working with database data. I use it daily in Excel.)

 

[Stormseed]

I thought SQL just works with Access?

SQL & Access both are different things but developed by the same vendor Microsoft. Both are RDBMS and are versatile to a wide number of Applications including Excel.

 

[sabsx]

I think there is something wrong with the getpivotdata formula...

now I have =IF(GETPIVOTDATA("price";pivot1!$A$3;"Q.Nr.";"20080318_4")>499999;"QB";"QS")

when I use this formula for the whole spreadsheet I just get QB