How do I get a pivot table to display a non-zero minimum value?

[#1]

Hi,

I have a data table with the purchasing data for a set of parts for 2010, 2011 & 2012. This data comprises, in some cases, of more than one purchasing price for an item in a given year. Eg.: A pen may have been purchased in 2010 at $ 10.00, and the same pen at a later part of the year at $ 15, and  $ 18. However, there is also a zero value transaction, where the vendor has given me this pen for $0.00 in order to regularize some older transaction.

In the example above, I would like the pivot table to display the minimum purchasing cost in 2010 to be $ 10.00 and not $0.00. What is the expression I should build around this..?

My data headers are as follows:

Part #	Unit Actual Price	Year
XXXX1	$ 10.00	2010
XXXX1	$ 12.00	2010
XXXX2	$ 7.00	2010
XXXX2	$ 8.00	2010
XXXX1	$ 15.00	2010

I need to be able to display the minimum non-zero price for each year..

Any form of help would be appreciated!

Thanks in advance

RL

[kaushiknsolanki]

Hi,

     Create a chart with dimensions as Part No and Year

     And expression as Min({<Price = {">0"}>}Price)

Regards,

Kaushik Solanki

[#3]

Kaushik.. Please read my earlier post to Jagan.. I need the price by each year in columns.. and not in rows (which is what would happen if I listed YEAR as a dimension..)..

[kaushiknsolanki · Accepted solution]

Hi,

     Sorry forgot to tell you that create a Pivot table and then drag the Year Column on top of the Expression.

Regards,

Kaushik Solanki

[jagan]

Hi,

I just used the same expression which kaushik given in earlier post and used the following script

LOAD

* INLINE [

Part #,    Unit Actual Price,    Year

XXXX1,    10,    2010

XXXX1,    12,    2010

XXXX2,    7,    2010

XXXX2,    8,    2010

XXXX1,    18,    2011

XXXX1,    10,    2011

XXXX1,    12,    2011

XXXX2,    7,    2012

XXXX2,    8,    2012

XXXX1,    18,    2012 ];

Created a Pivot chart with Part# and Year as dimensions, PFA screenshot of the same.

Hope this helps you.

Regards,

Jagan.

[#6]

Works fine now.. Thanks so much for your time Jagan & Kaushik..!!