Filter by top N changes from last month to this month

1
votes

I have a dataset of parts, price per part, and month. I am accessing this data via a live connection to a SQL Server database. This database gets updated monthly with new prices for each part. What I would like to do is graph one year of price data for the ten parts whose prices changed the most over the last month (either as a percentage of last month's price or as a total change in dollars.)

Since my database connection is live, ideally Tableau would grab the new price data each month, updating the top ten parts whose prices changed for the new period. I don't want to manually have to change the months or use a stored procedure if possible.

part   price      date
110    167.66     2018-12-01 00:00:00.000
113    157.82     2018-12-01 00:00:00.000
121    99.16      2018-12-01 00:00:00.000
133    109.82     2018-12-01 00:00:00.000
137    178.66     2018-12-01 00:00:00.000
138    154.99     2018-12-01 00:00:00.000
143    67.32      2018-12-01 00:00:00.000
149    103.82     2018-12-01 00:00:00.000
113    167.34     2018-11-01 00:00:00.000
121    88.37      2018-11-01 00:00:00.000
133    264.02     2018-11-01 00:00:00.000
4
sqlsql-servertableau-api
Very interesting question on how to determine TOP 10 by Price & Percentage. Would say you have 20 items. 10 of them increased in price from $100 to $110 (10%) and another 10 items increased from $1 to $2 (100%) - Which Items would you show? – Slava Murygin
@slava-murygin I'm curious about ways to show both because I'm not sure which will be more beneficial in the long run. – ahh_real_numbers
ahh_real_numbers Can you provide table definition? Do you want only last moth? – Slava Murygin
Sorry added a sample above. – ahh_real_numbers

4 Answers

1
votes
  1. Create a calculated field called Recent_Price as if DateDiff(‘month’, [date], Today()) <= 1 then [price] end. This returns the price for recent records and null for older records. You might need to tweak the condition based on details, or use an LOD calc to always get the last 2 values regardless of today’s date.
  2. Create a calculated field called Price_Change as Max([Recent_Price]) - Min([Recent_Price]) Note you can’t tell from this whether the change was positive or negative, just its magnitude.
  3. Make sure part is a discrete dimension. Drag it to the Filter Shelf. Set the filter to show the the Top N part by Price_Change

It’s not hard to extend this to include the sign in the price change, or to convert it a percentage. Hint, you’ll probably need a pair of calcs like that in step 1 to select prices for specific months

0
votes

You haven't provided any sample data, but you could follow something like this,

;WITH top_parts AS (
    -- select the top 10 parts based on some criteria
    SELECT TOP 10 parts.[id], parts.[name] FROM parts
    ORDER BY <most changed>
)
SELECT price.[date], p.[name], price.[price] FROM top_parts p
INNER JOIN part_price price ON p.[id] = price.[part_id]
ORDER BY price.[date]
  1. Use a CTE to get your top parts.
  2. Select from the CTE, join to the price table to get the prices for each part.
  3. Order the prices or bucketize them into months.
  4. Feed it to your graph.
0
votes

It will be something like this for just one month. If you need the whole year you have to specify clearly what exactly you want to see:

;WITH cte as (
    SELECT TOP 10 m0.Part
        , Diff = ABS(m0.Price - m1.Price)
        , DiffPrc = ABS(m0.Price - m1.Price) / m1.Price
    FROM Parts as m0
    INNER JOIN (SELECT MaxDate = MAX([Date] FROM Parts) as md
        ON md.MaxDate = m0.[Date]
    INNER JOIN Parts as m1 ON m0.Part = m1.Part and DATEADD(MONTH,-1,md.MaxDate) = m1.[Date]
    ORDER BY ABS(m0.Price - m1.Price) DESC
    -- Top 10 by percentage:
    -- ORDER BY ABS(m0.Price - m1.Price) / m1.Price DESC
)
SELECT * FROM Parts as p
INNER JOIN cte ON cte.Part = p.Part
0
votes
-- Input from user,you decide in which format last month date will be pass
-- In other words , @InputLastMonth  is parameter of proc
--Suppose it pass in yyyy-MM-dd manner
Declare @InputLastMonth date='2018-12-31'

-- to get last one year data
--Declare local variable which is not pass
declare @From date= dateadd(day,1,dateadd(month,-12, @InputLastMonth))
Declare @TopN int=10-- requirement

--select @InputLastMonth,@From

Select TOP (@TopN) parts,ChangePrice
(
select parts,ABS(max(price)-min(price)) as ChangePrice
from dbo.Table1
where dates>=@From and dates<=@InputLastMonth
group by parts
)t4
order by ChangePrice desc

By change most ,I understand that,suppose there is one parts 'Part1' which was price 100 in first month and change to 1000 in last months. On the other hand Part2 change several times during same period but final change was only 12.

In other word Part1 change only twice but change difference was huge,Part2 change several time but change difference was small.

So Part1 will be preferred.

Second thing is change can be negative as well as positive.

Correct me if I have not understood your requirement.