Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
jayaseelan
Creator III
Creator III

Get Cumulative for particular Date

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. 

Capture.PNGCapture1.PNG

My Output should be shown below.

Capture2.PNG

Thanks,

1 Solution

Accepted Solutions
sunny_talwar

Works for me

Capture.PNG

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

Capture.PNG

View solution in original post

5 Replies
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
Creator III
Creator III
Author

Hi sunny Talwar,

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

Capture3.PNG

The above output is from your script.

but i need,

Capture2.PNG

Thanks

sunny_talwar

Works for me

Capture.PNG

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

Capture.PNG

jayaseelan
Creator III
Creator III
Author

Hi Sunny,

Thank you so much its working...