topic Re: How to build the a sum with differnet start dates? in QlikView

How to build the a sum with differnet start dates?

chrisg — Mon, 08 Feb 2016 09:25:13 GMT

Hello,

i have my articles with the date of the last inventory. I want to determine the number of the products that I have bought since the last inventory up to a certain date .

For example: For the Product Tee: From the inventory date 1.1.2016 up to 31.1.2016

= 30 units

(Date dd,mm,yyyyy)

Date	Units
31.12.2015	10
07.01.2016	15
01.02.2016	15
05.02.2016	20
10.02.2016	15

How can I solve this in one table?

Many thx

best regards

Christoph

Re: How to build the a sum with differnet start dates?

sunny_talwar — Mon, 08 Feb 2016 09:52:13 GMT

May be this:

=Sum(If(Datum >= [Inventory Date] and Datum <= vDate, Units))

Where vDate comes from user input

Re: How to build the a sum with differnet start dates?

chrisg — Mon, 08 Feb 2016 10:44:16 GMT

Many Thx! ;Looks good!

Re: How to build the a sum with differnet start dates?

sunny_talwar — Mon, 08 Feb 2016 10:50:35 GMT

Alternatively if you have a unique combination of each row, you can also use Set analysis:

LET vDate = '1.1.2016';

Inventory:

LOAD * INLINE [

ID, Article , Inventory Date

1, Tee, 01.01.2016

2, Milch, 20.12.2015

3, Zucker, 24.12.2015

4, Brot, 01.01.2016

];

Purchase:

LOAD *,

RowNo() as Key;

LOAD * INLINE [

ID, Units, Datum

1,10,31.12.2015

1, 15, 07.01.2016

2, 15, 07.12.2015

1, 15, 10.02.2016

1, 15, 01.02.2016

1, 20, 05.02.2016

2, 5, 22.12.2015

2, 10, 15.01.2016

2, 4, 05.02.2016

3,10,31.12.2015

3, 15, 07.01.2016

3, 15, 07.12.2015

4, 15, 10.02.2016

4, 15, 01.02.2016

4, 20, 07.03.2016

4, 5, 22.12.2015

4, 10, 19.01.2016

4, 4, 06.02.2016

];

I created Key which is Unique for each ID and Datum. Now I can use this in my set analysis like this:

=Sum({<Key = {"=Datum >= [Inventory Date] and Datum <= vDate"}>} Units)