16 Replies Latest reply: Jun 12, 2015 11:42 AM by Sunny Talwar

# Help with preceding load

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

• ###### Re: Help with preceding load

Try it like this:

Table2:

LOAD SKU#,

[Sum of Units packed in recent 30 days],

(TODAY() - MinDate30) AS [Diff. of days in 30 days time frame],

[Sum of Units packed in recent 30 days]/(TODAY() - MinDate30) as Velocity;

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;

Join (Table2)

LOAD Sum(Velocity) as [Total Velocity]

Resident Table2;

Table3:

LOAD *,

Velocity/[Total Velocity] as [Relative Velocity %]

Resident Table2;

Drop Table Table2;

• ###### Re: Help with preceding load

Hi Expert,

But i have a question here. Do i need to specify "Group by SKU#" in Table 3 too???

• ###### Re: Help with preceding load

I don't think so. because right now everything in Table2 is already grouped by SKU#. and Mark, I am no Expert, I am still learning just like you. So you can call me Sunny.

Best,

Sunny

• ###### Re: Help with preceding load

stalwar1

Your script did the magic sunny.

One last question friend.

I'm trying to do the full accumulation for [Relative Velocity %] in the script, because i want it as a dimension so as to seperate the accumulation into buckets per %.

Is "rangesum(above(sum([Relative Velocity %]),0,rowno(total )))" accurate??

• ###### Re: Help with preceding load

When i try the expression in CHart tbale, it just hangs the qlikview

• ###### Re: Help with preceding load

Add this after table 3

Table4:

LOAD *,

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

Resident Table3

Order By ...;

Drop Table Table3;

You will need a order by statement to make sure the accumulation occurs in the order you want.

Give it a shot and see if it works.

Best,

Sunny

• ###### Re: Help with preceding load

Hi Sunny,

I tried it with order by Relative Velocity%. But it is not in order.

First column shows the Relative velocity, 2nd column is the one which should look like.

3rd column is the one which is loaded by the script.

• ###### Re: Help with preceding load

Ultimately i want to seperate  the SKUs with respect to the Accumulated percentage.

0-25, 26-50, and 51-100.

So, i was trying to load from the script.

• ###### Re: Help with preceding load

Its loading the exact reverse order.

instead of 0-100, its loading 100-0

• ###### Re: Help with preceding load

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

• ###### Re: Help with preceding load

Still the same ..

• ###### Re: Help with preceding load

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;

• ###### Re: Help with preceding load

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.

• ###### Re: Help with preceding load

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.

• ###### Re: Help with preceding load

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.

• ###### Re: Help with preceding load

Awesome

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