[IDT SQL] Create a measure to get the latest quote price

[Former Member]

Hi IDT and SQL Experts,

I am having a problem creating a price measure with the condition where only shows the price that has the latest quote date.

My requirement is to get only latest quote price that has been quoted, and if the part has more than one same latest quote dates, we get the maximum price among those same dates.

Example for part "BC20135", it has several quote prices and different quote dates:

[NO]     [Quote Date]     [Quote Price]
1.          04/12/2015        $12,000
2.          04/12/2015        $15,250
3.          01/20/2015        $18.400
4.          08/05/2014        $19,000
5.          12/10/2012        $12,000

In this case, since we have the same latest quote date of 04/12/2015, we should get the highest quote price which is No.2 - "$15,250".

The problem is when I create the measure "Quote Price" and set the Projection Function as "Max" and SQL -"SELECT MAX(Quote.Unit_Price), and dimension "Quote Date" as "SELECT MAX(Quote.Quote_Date). it returns me one line item for Part BC20135 as 04/12/2015 with price "$19,000", which is not the correct price with the right date.

Anyone has any idea what I can do for this one? Even create a derived table, I cannot achieve the right result.

Please help me on this.

Thank you.

[former_member207052 · Accepted solution]

As you had mentioned you can achieve this with derived table.

Derived Table:

Output while using only the objects from Derived table:

Output when using all objects:

Derived Table Definition:

SELECT

  yourtable.Part,

  yourtable.QDate,

  Max(yourtable.Number) as MaxProdNumber,

  Max(yourtable.QuotePrice) as MaxPrice

FROM

  yourtable

  Group by   yourtable.QDate,  yourtable.Part

[Former Member · Accepted solution]

Thank you Narashimman. I tried that codes and it is getting me all the values for the part. But I only want to show the quote price that has the latest quote date.

So, for that part BC20135, i only want to have one line when execute the query which is "04/12/2015        $15,250".

Do you know how to achieve that?

Thank you .

[Former Member · Accepted solution]

I wasn't make it clear that the [NO] field was something I made up, we only consider the quote date and quote price for each part.

It will be like:

Part	date	Value
123	4/12/2015	250
123	4/12/2015	100
123	3/15/2015	350
456	4/13/2015	280
456	3/5/2015	300
456	2/25/2015	350
Expected Results:
123	4/12/2015	250
456	4/13/2015	280

Thank you.

Lily Kong

[Former Member · Accepted solution]

Hi Lily,

1) I have created a Table in Database on my side with some data for two products to replicate your issue. Different Net Sales on same Day for product.

2) Created Derived Table in Universe (via IDT)

3) Joined Original Table and New Derived Table on Two fields (Performance might be bit slow if those fields not indexed for bigger tables)

4) Created New Measure (Note the Green Highlighted part, This is critical)

5) Create a report using below fields and output as follows for EACH Product.

-Thanks,

Deepu.

[Former Member · Accepted solution]

Hi Deepu,

Thanks for your reply!

For Step 4, when I created the measure, it only give me the original table ( Dim_Fact) on the TABLES field, how can I add the DT_FACT table on there?

Lily 

[Former Member · Accepted solution]

Highlighted in Green (click at end "...")

Select Derived Table

-Thanks,

  Deepu Reddy.

[Former Member · Accepted solution]

got it! thank you so much!!! it works!!

[Former Member · Accepted solution]

Hi Deepu,

Thank you for the answer.

It works only with the 2 tables together but when I add other fields from other tables, the records are not correct and it is getting duplicates.

For example, I add the sales price from sales table to the query and it returns me this:

part	date	value	SALES PRICE
123	4/12/2015		300
123		250
456	4/13/2015		950
456		280
789	3/10/2015		120
789		100

Where it should be:

part	date	value	SALES PRICE
123	4/12/2015	250	300
456	4/13/2015	280
789	3/10/2015	100	120

Please help.

Thanks.

[former_member207052 · Accepted solution]

Use these formulae:

For Date column: =Max([your_date]) in ([Part])

For Value column: =Max([value]) in ([Part])

Regards,

Naras

[Former Member · Accepted solution]

Thank you Narashimman.

The DATE dimension has the correct SQL on it because it is the main base of latest date, but we do not want JUST the highest value on that part, we want the highest value from the latest date.

So, if there are 2 different values with the same latest date and the highest value has a older date, we want to use the highest value one from that 2 same date(latest)

so, i think the SQL for value column shows only the highest value among all date, right?

Thanks.

Lily

[former_member207052 · Accepted solution]

From the data you provided above, I see two things

1. Where there is a date the value is empty
2. Where the date is empty there is a value.

So, Yes, if applying Max on Value works for you well and good. I had suggested you to put Max on both the columns as there are no two dates in your data for the same part.

Regards,

Naras