How to show minimum by Month

[#1]

I have been trying to figure this out for a while now, and I just can't seem to get it.  I have a table with prices paid for individual part numbers.  So my data looks something like this:

Part Number               Month               Price

X-123                         January               6.56

X-123                         January               6.43

X-123                         January               6.43

X-123                         Februay               6.43

Y-456                         January               1.23

Y-456                         Februay               1.25

Y-456                         February              1.21

I've got a pivot table that lists out the unique values per month, I just need to add a column that shows the minimum value for the part number by month. 

So I'm trying to show:

Part Number               Month               Price          Minimum Price

X-123                         January               6.56               6.43

X-123                         January               6.43               6.43

X-123                         January               6.43               6.43

X-123                         Februay              6.23                6.23

Y-456                         January               1.23               1.23

Y-456                         Februay               1.25               1.21

Y-456                         February              1.21               1.21

Any help in getting a formula to calculate the Minimum Price field would be great, thanks. 

[johnw]

I think something like this, assuming your column headings match your underlying field names.

min(total <"Part Number","Month"> "Price")

[#3]

I tried this:  =MIN(TOTAL <"Reporting Part #","Month"> "PO Unit Price Per 1 USD")

Unfortunately, this isn't working.  These are the underlying columns and field names:

Dimensions: 

REPORTINGPARTNUM         Label: Reporting Part #

=$(v_MnthDt)                        Label: Month

Expression: 

=$(v_UnitPrice)                      Label: PO Unit Price Per 1 USD

Not sure if I got the syntax wrong?

[johnw]

You need to use the underlying field names in the expression.  So...

min(total <REPORTINGPARTNUM,???> ???)

The question marks are because I have no idea what's in your v_MnthDt or v_UnitPrice variables.  What's in them?  It's possible that this would work:

min(total <REPORTINGPARTNUM,$(v_MnthDt)> $(v_UnitPrice))

But I doubt it.

[#5]

I created those variables to create a month-year field and I needed to calculate the unit price by from the total spend and the quantity ordered.  So the variables =

v_MnthDt = monthname(TRANSACTION_DATE)

v_UnitPrice = (PO_LN_ITM_NET_PRC_USD_AMT/PO_LN_ITM_NET_PRC_UNIT_QTY)

Do I need to create those fields in the script when I pull in the data, so that way it doesn't reference variables? I'll give that a shot just to see if that works.  Thanks. 

[johnw · Accepted solution]

I would definitely create the month in the script, yes.  So somewhere in your load:

monthname(TRANSACTION_DATE) as TRANSACTION_MONTH,

Or as I'd personally do it, create a calendar table to hold any date-related fields:

[Transaction Calendar]:
LOAD *
,monthname(TRANSACTION_DATE) as TRANSACTION_MONTH
;
LOAD date(fieldvalue('TRANSACTION_DATE',recno())) as TRANSACTION_DATE
AUTOGENERATE fieldvaluecount('TRANSACTION_DATE')
;

You don't need to do the other in the script, though.  You could, but you don't need to.  This should then work:

min(total <REPORTINGPARTNUM,TRANSACTION_MONTH> PO_LN_ITM_NET_PRC_USD_AMT/PO_LN_ITM_NET_PRC_UNIT_QTY)

[#7]

That worked perfectly, thank you very much.