Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
i have a table with these columns Area,Country,Product,Date,Quantity
it's a transactions table i want to evaluate the stock every day and for the missing dates in the table
how to do that?
Thanks.
Like This?
Hi
I suggest that you add a master calendar that includes all the dates from earliest to latest transaction. Keep it as a separate table, and associate the transaction Date and calendar Date (by giving them the same name).
There are many links if you search here on "master calendar".
HTH
Jonathan
not totally like it
the table i have is a transactions table like this
Area | Branch | Product | Date | Qty |
---|---|---|---|---|
A | A1 | X | 1/1/2010 | 100 |
A | A2 | Y | 3/1/2010 | 200 |
B | B1 | X | 5/1/2010 | 100 |
A | A3 | Y | 8/1/2010 | 150 |
B | B2 | X | 6/1/2010 | 30 |
C | C1 | Y | 7/1/2010 | 20 |
A | A1 | X | 9/1/2010 | 10 |
B | B2 | Y | 2/1/2010 | 15 |
C | C2 | X | 3/1/2010 | 20 |
I want the result to be
Area | Branch | Product | Date | Qty |
---|---|---|---|---|
A | A1 | X | 1/1/2010 | 100 |
A | A1 | X | 9/1/2010 | 110 |
And so on, @ 9/1/2010 for the same product the quantity which is 10 is added to the start quantity of that product which is 100
any help?
no i'm dealing with a transactions table
i want to add the transactions for a product to evaluate the stock every day
Like this?
Temp:
Load *, Area&Branch&Product as Key Inline
[
Area, Branch, Product, Date, Qty
A, A1, X, 1/1/2010, 100
A, A2, Y, 3/1/2010, 200
B, B1, X, 5/1/2010, 100
A, A3, Y, 8/1/2010, 150
B, B2, X, 6/1/2010, 30
C, C1, Y, 7/1/2010, 20
A, A1, X, 9/1/2010, 10
B, B2, Y, 2/1/2010, 15
C, C2, X, 3/1/2010, 20
];
Final:
Load
Area,
Branch,
Product,
Date,
Qty,
IF(Key = Previous(Key), Peek(CumulativeStock)+Qty, Qty) as CumulativeStock
Resident Temp
Order By Area,Branch,Product, Date;
Drop Table Temp;
Update : Date added in Order By
Hi Mona,
you can Try this expression :
sum(aggr(sum(Qty)+ RangeSum(above(sum(Qty),1,RowNo()-1)),Area,Branch,Product,Date))
PFA
good the idea to use
Area&Branch&Product as Key
to avoid a lot of peek
with real data you probably need to add the Date at the end of Order By Area,Branch,Product
was this helpful ?
thanks Manish,
but when i try to make a straight table with Product and Date as dimensions and sum(CumulativeStock) as the expression the result i supposed to be the summation of the quantity for product x in day 1/1/2010 for all the areas and branches but the result was not as i supposed.
i.e i want it to be flexible to sum cumulativestock for any dimension i want.