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,

     Try this Expression.

     Min({<Price = {">0"}>}Price)

Regards,

Kaushik Solanki

[#3]

Hi Kaushik..

Thanks for the expression.. It does work, but i'm not too sure where to fit in the year; i.e, min. price in 2010.. min price in 2011 etc. I want to understand how I could incorporate that into the expression.. Could you help me with that as well..?

Thanks in advance,

RL

[jagan]

Hi,

Create a Pivot table with Part # and Year as dimension and give the expression

=Min({<Price = {">0"}>}Price)

as Kaushik Solanki suggested.  Qlikview will automatically display Minimum price > 0 by Year and Part#.

Hope this helps you.

Regards,

Jagan.

[kaushiknsolanki]

Hi,

     Please share with us in what format you need the output.

Regards,

Kaushik Solanki

[#6]

Hi Jagan & Kaushik,

Adding "YEAR" to dimension shows prices one below the other (each year in one row.. For part X, price in year 2010, 2011 n 2012 in 3 rows, one below the other for each year... That is, as below:

Part No.	Min. Price
XXXX1
2010	10
2011	12
2012	18

While what we're trying to achieve needs the prices to be displayed next to each other in the same row..

Part No.	Min. Price - 2010	Min. Price -2011	Min. Price - 2012
XXXX1	10	12	18

I hope this is comprehendible.. Please let me know if it isn't .. i'll try n use a better representation..

Thanks,

RL

[jagan]

Hi,

Drag (Pivot) year dimension to the top of the row, so that this can be achieved.

Regards,

jagan.

[#8]

This still gives me the year as row labels.. I need minimum price by each year to be listed in adjacent columns by part number, as shown in the sample table..

From whatever little I know of QV, I believe that putting the YEAR in dimension, will only continue to make it a row label.. Hence, I think it should come from the expression that the "Price" for "YEAR" - 2010 should be in one column, ""Price" for "YEAR" - 2011 should be in the next column.. so on ..

[jagan]

Hi,

Hope attached file helps you.

Regards,

Jagan.

[#10]

Jagan.. I'm using a personal edition of QV and have exhausted my free count of document recoveries.. could you please copy-paste the expression/dimension here..?? Sorry about the trouble..!!