I am working on counting how many times the promise date for an order changed over time. The pivot table I made looks like this:\\\
Order
3/2/2015
3/3/2015
3/4/2015
2312323
5/3/2015
5/6/2015
5/10/2015
1234324
6/3/2015
6/3/2015
6/3/2015
5678901
4/16/2015
4/19/2015
4/16/2015
The dates in the data cells are the promise dates. My question is if there's a way of counting how many times each order's promise dates changed during the life of the order? Currently I am using Count(Distinct()) but in a case like Order #5678901, it would only count that it changed once because it went back to the same promise date.
This is my current expression in the table where I am counting: if((count(distinct([Promise Date]))-1)>=0,(count(distinct([Promise Date]))-1),'-')
The results I would want in this example would be:
Order, # of Changes
2312323, 2
1234324,0
5678901,2
I'm not sure if this is possible but I would appreciate any help I can get.
Hi there! I am back with another question! So I am concatenating my tables together because I was ending up with synthetic keys that were actually messing up my data. But now, the script you gave to me isn't working. It was fine up until I concatenated them, and now it's coming up with a wrong answer. Am I putting the concatenation in the wrong place?
[Aged Orders - March_temp]:
LOAD Date(Date#(Mid(FileName(),6,6),'MMDDYY')) as TXTDate,
//Above line is for pulling date from file name
[Prom Days Out],
[Sales Doc.],
Item,
Upper(Status) as Status,
Material as %Material,
Material,
Req.dlv.dt,
[Deliv. dat],
Plnt,
[Order quantity],
[Promise Date],
[Sales Doc.] & Item as [Unique Order No.],
[Sls Org],
[Cat#],
[Material Code],
[Open Order($)],
[On Delivery($)],
[Not On Del ($)]
FROM
[\\gnrfdfp001\Purchasing\Supply Chain Reports\Aged Shortage Report By Plant\2015 Files\Aged 03*.xlsx]
(ooxml, embedded labels, header is 4 lines, table is [Aged All]);
[Aged Orders - March]:
load *,
if(([Unique Order No.])=Peek([Unique Order No.]) and [Status]<>Peek([Status]), 1, 0) as StatusChanged,
if(([Unique Order No.])=Peek([Unique Order No.]) and [Promise Date]<>Peek([Promise Date]), 1, 0) as PromiseChanged
Resident [Aged Orders - March_temp]
order by [Unique Order No.], TXTDate;
drop Table [Aged Orders - March_temp];
[Aged Orders - April_temp]:
LOAD Date(Date#(Mid(FileName(),6,6),'MMDDYY')) as TXTDate,
//Above line is for pulling date from file name
[Prom Days Out],
[Sales Doc.],
Item,
Upper(Status) as Status,
Material as %Material,
Material,
Req.dlv.dt,
[Deliv. dat],
Plnt as Plant,
[Order quantity],
[Promise Date],
[Sales Doc.] & Item as [Unique Order No.],
[Sls Org],
[Cat#],
[Material Code],
[Open Order($)],
[On Delivery($)],
[Not On Del ($)],
[Name]
FROM
[\\gnrfdfp001\Purchasing\Supply Chain Reports\Aged Shortage Report By Plant\2015 Files\Aged 04*.xlsx]
(ooxml, embedded labels, header is 4 lines, table is [Aged All]);
[Aged Orders - April]:
concatenate ([Aged Orders - March])
load *,
if(([Unique Order No.])=Peek([Unique Order No.]) and [Status]<>peek([Status]), 1, 0) as StatusChanged,
if(([Unique Order No.])=Peek([Unique Order No.]) and [Promise Date]<>Peek([Promise Date]), 1, 0) as PromiseChanged
