Task

[Janmeet]

DATA contains date and closing stock for the day. Closing stock for the month is equal to the value of closing stock at the end of the month . (eg: closing stock APR = closing stock on 30th APR).

case 1. Least closing stock value in the last 6 months has to be subtracted from the stock value of current month's stock for everyday.

case2. If  Least closing stock value in the last 6 months is less than the closing stock of any day of the current month then find the least value of closing stock from the current month and subtract it from each day's stock.

[williejacobs]

Hi @Janmeet ,

I would reload the data and join back to your closing stock value eg.

Left Join (DATA)
Load Min([Closing Stock] as 'MinClosingStock'
Resident DATA;

If you a stock code as well, you will add this to your load and Group by this dimension.

For case 2
I would start by creating a month dimension on your data set and then use the same method as described above but loading and grouping by the month dimension as well.

[Bianchi]

// Loading data from CSV

filestock_data:
LOAD
DATE,
CLOSING_STOCK
FROM
[stock_data.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

// Converting the DATE column to date

formatstock_data:
LOAD
DATE,
Date#(DATE, 'M/D/YYYY') AS DateFormatted,
CLOSING_STOCK
RESIDENT
stock_data;

// Finding the lowest closing value in the last 6 months

LastSixMonthsMinClosingStock:
LOAD
Min(CLOSING_STOCK) as MinClosingStockLastSixMonths
RESIDENT
stock_data
WHERE
DateFormatted >= AddMonths(Today(), -6);

// Joining the data with the lowest closing value of the last 6 months

JOIN
LOAD
MinClosingStockLastSixMonths
RESIDENT
LastSixMonthsMinClosingStock;

// Calculating the difference for each date

stock_data_with_difference:
LOAD
DATE,
DateFormatted,
CLOSING_STOCK,
MinClosingStockLastSixMonths,
If(MinClosingStockLastSixMonths < CLOSING_STOCK, CLOSING_STOCK - MinClosingStockLastSixMonths, CLOSING_STOCK - Min(CLOSING_STOCK)) AS StockDifference
RESIDENT
stock_data;

// Saving data to be used in visualizations
DROP TABLE LastSixMonthsMinClosingStock;
DROP FIELD DateFormatted;