Help with preceding load

[markgraham123]

I have SKU, Shipdate and Units packed in the table.

1. Find the recent 30 days from today.   

2. selecting the first ship date in the retrieved 30 days.   

3.  Calculating diff, between today and the retrieved first ship date. 

I can get these with the below preceding load script.

LOAD SKU#, [Sum of Units packed in recent 30 days],(TODAY() - MinDate30) AS [Diff. of days in 30 days time frame];

LOAD SKU#,Sum([Units Packed 30]) as [Sum of Units packed in recent 30 days], Min(FilteredDate30) AS MinDate30

GROUP BY SKU#;

LOAD SKU#, [Units Packed] AS [Units Packed 30],[Ship Date] AS FilteredDate30

RESIDENT Table1

WHERE [Ship Date] > TODAY()-31;

But i need to calculate,

velocity = ([Sum of Units packed in recent 30 days]/[Diff. of days in 30 days time frame]) and

Relative velocity % per sku with the formula, Sum(Total(Velocity))/Velocity per SKU#.

Can someone help.

I'm struck

[sunny_talwar]

Looks like the exact opposite

Are you doing Order By [Relative Velocity%] asc or desc? By default it would be asc, so whatever it is, try the opposite and see if that works.

Best,

Sunny

[markgraham123]

Still the same ..

[sunny_talwar · Accepted solution]

This isn't working for you?

Table4:

LOAD *,

          Alt(Peek('Cumulative Relative Velocity %'), 0) + [Relative Velocity %] as [Cumulative Relative Velocity %]

Resident Table3

Order By [Relative Velocity%] desc;

[markgraham123]

Hi Sunny,

Velocity or Relative Velocity %, for both its taking least value as starting point and accumulating.

But here, taking highest point as starting point and accumulation needs to be done.

[sunny_talwar]

I understand that is what is happening, but when I tell it to order by something desc - it should take the maximum value and order it that way. I am not sure why would it still do an asc order when we are specifying it to do desc.

[markgraham123]

Sunny,

Its working Perfect.

You are really awesome and genius.

If time permits,It would be my pleasure to learn few concepts in preceding load from you.

[sunny_talwar]

Awesome

I am glad it worked out for you. Just follow me (I have already followed you) and we can message each other.