topic Re: Get Cumulative for particular Date in QlikView

Get Cumulative for particular Date

jayaseelan — Tue, 07 Mar 2017 11:41:16 GMT

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,

Re: Get Cumulative for particular Date

sunny_talwar — Tue, 07 Mar 2017 12:12:23 GMT

You need this in the script?

Re: Get Cumulative for particular Date

sunny_talwar — Tue, 07 Mar 2017 12:25:25 GMT

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];

Re: Get Cumulative for particular Date

jayaseelan — Tue, 07 Mar 2017 13:00:07 GMT

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

Re: Get Cumulative for particular Date

sunny_talwar — Tue, 07 Mar 2017 13:07:06 GMT

Works for me

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

Re: Get Cumulative for particular Date

jayaseelan — Tue, 07 Mar 2017 13:11:24 GMT

Hi Sunny,

Thank you so much its working...