topic Re: How do I get a pivot table to display a non-zero minimum value? in QlikView

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

Tue, 26 Mar 2013 08:34:43 GMT

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

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

kaushiknsolanki — Tue, 26 Mar 2013 09:18:42 GMT

Hi,

Try this Expression.

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

Regards,

Kaushik Solanki

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

Tue, 26 Mar 2013 11:03:31 GMT

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,

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

jagan — Tue, 26 Mar 2013 11:15:45 GMT

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.

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

kaushiknsolanki — Tue, 26 Mar 2013 11:19:10 GMT

Hi,

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

Regards,

Kaushik Solanki

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

Tue, 26 Mar 2013 11:26:12 GMT

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,

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

jagan — Tue, 26 Mar 2013 11:32:49 GMT

Hi,

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

Regards,

jagan.

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

Tue, 26 Mar 2013 11:37:47 GMT

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 ..

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

jagan — Tue, 26 Mar 2013 11:38:24 GMT

Hi,

Hope attached file helps you.

Regards,

Jagan.

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

Tue, 26 Mar 2013 11:47:52 GMT

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..!!

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

kaushiknsolanki — Tue, 26 Mar 2013 12:10:05 GMT

Hi,

Create a chart with dimensions as Part No and Year

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

Regards,

Kaushik Solanki

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

Tue, 26 Mar 2013 12:12:14 GMT

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..)..

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

kaushiknsolanki — Tue, 26 Mar 2013 12:17:50 GMT

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

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

jagan — Tue, 26 Mar 2013 12:28:43 GMT

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.

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

Tue, 26 Mar 2013 12:32:21 GMT

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