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]

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;

[markgraham123]

Hi Expert,

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

[sunny_talwar]

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

[markgraham123]

sunindia‌

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??

[markgraham123]

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

[sunny_talwar]

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

[markgraham123]

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.

[markgraham123]

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.

[markgraham123]

Its loading the exact reverse order.

instead of 0-100, its loading 100-0