Get Cumulative for particular Date

[jayaseelan]

Hi Experts,

             It's a time to help for me. I have an Doubt on script editor, 

             I have an data like shown below, and In another sheet I will give Stock dates.

             For the the given Stock Date  my Qty should be cumulative from above values from the Qty field. 

My Output should be shown below.

Thanks,

[sunny_talwar]

You need this in the script?

[sunny_talwar]

May be this:

Table:

LOAD * INLINE [

    ItemID, Qty, Date

    A, 2, 12/1/2016

    A, -1, 12/6/2016

    A, 5, 1/23/2017

    B, 1, 12/6/2016

    B, 2, 1/1/2017

    A, -2, 2/2/2017

    B, 3, 1/17/2017

];

[Stock Date]:

LOAD *,

  MakeDate(2016, 12, 1) as StartDate

INLINE [

    Stock Date

    12/31/2016

    1/30/2017

    2/28/2017

];

Left Join ([Stock Date])

IntervalMatch(Date)

LOAD StartDate,

  [Stock Date]

Resident [Stock Date];

Left Join ([Stock Date])

LOAD *

Resident Table;

DROP Table Table;

FinalTable:

LOAD ItemID,

  [Stock Date],

  Sum(Qty) as Qty

Resident [Stock Date]

Group By ItemID, [Stock Date];

DROP Table [Stock Date];

[jayaseelan]

Hi sunny Talwar,

       Thanks for your valuable reply, yours Script output not matching with my output.

The above output is from your script.

but i need,

Thanks

[sunny_talwar · Accepted solution]

Works for me

B, 1/30/2017 should be 6 I think because all B activities are before Feb

[jayaseelan]

Hi Sunny,

Thank you so much its working...