Date-Difference Related Calculation

[Anonymous]

Hi Everyone,

I would much appreciate your help with the following:

I have a table with a Date Column, CustomerID, ProductID and a Column of Production Percentage for each ProductID.

This Percentage is for the End of the Month in each line.

Here is a sample:

Date	CustomerID	ProductID	Production % at End of Month
31/05/2017	4444	10	70%
30/06/2017	4444	10	90%
31/05/2017	4444	20	30%
30/06/2017	4444	20	60%
31/05/2017	9999	10	0%
30/06/2017	9999	10	100%
31/05/2017	9999	20	0%
30/06/2017	9999	20	0%
31/07/2017	9999	20	80%

So, for example, ProductID 10 for customer 4444 was at 70% at the end of May and at 90% production at the end of June.

That means that During June Production % was 20% (=90% - 70%).

Another example: ProductID 20 for customer 9999 was at 0% at the end of May and also at the end of June. This product was at 80% production at the end of July. Therefore, during July Production % for this product was 80%.

What I would like to ask is what kind of calculation should I use to create this difference of % in production for each product?

Here is how I would like the Difference to show, more or less:

CustomerID	ProductID	June	July
4444	10	20%	0%
4444	20	30%	0%
9999	10	100%	0%
9999	20	0%	80%

Hope my question is clear.

and thanks in advance for any response.

Sandra

[antoniotiman · Accepted solution]

Hi Sandra,

try like this

Temp:

LOAD MonthName(Date) as Month,

     CustomerID,

     ProductID,

     Num([Production % at End of Month]) as %Production

FROM [lib://File]

(html, codepage is 28591, embedded labels, table is @1);

NoConcatenate LOAD *,If(CustomerID=Peek(CustomerID) and ProductID=Peek(ProductID),

RangeSum(%Production,-Peek(%Production)),%Production) as Diff

Resident Temp Order By CustomerID,ProductID,Month;

Drop Table Temp;

Regards,

Antonio

[felipedl]

Hi Sandra,

Try the following

x:

Load * Inline

[

Date,CustomerID,ProductID,Production % at End of Month

31/05/2017,4444,10,70%

30/06/2017,4444,10,90%

31/05/2017,4444,20,30%

30/06/2017,4444,20,60%

31/05/2017,9999,10,0%

30/06/2017,9999,10,100%

31/05/2017,9999,20,0%

30/06/2017,9999,20,0%

31/07/2017,9999,20,80%

];

NoConcatenate

data:

Load

RowNo() as Id,

MonthName(Date) as Month,

*,

fabs(if(RowNo()=1 or ProductID<>Previous(ProductID),num([Production % at End of Month],'#0,0'),(1-[Production % at End of Month])-(1-previous([Production % at End of Month])))) as NewPercentage;

Load

Date,

CustomerID,

ProductID,

[Production % at End of Month]

Resident x

order by CustomerID,ProductID,Date;

drop table x;

The above code gives me the picture bellow.

Felipe.

[Anonymous]

Hi Antonio

Thanks a lot for your reply. It was a great help and ultimately worked well.

I had a problem, which I managed to solve somehow by now -

My Fact table was actually concatenated with other tables. and the noconcatenate clause created a problem.

What I did to solve this was to create another Load and using Resident to the the last table in the script.

Anyway Thanks a lot. Brilliant.

Sandra

Thanks for your reply as well. felipedl

[antoniotiman]

Sandra,

don't worry, You can remove my 'NoConcatenate' prefix.

Also, if You want 0 % value instead of - sign, then replace Expression with

RangeSum(Diff)

Regards,

Antonio