Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

chrisg
Not applicable

How to build the a sum with differnet start dates?

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)

DateUnits
31.12.201510
07.01.201615
01.02.201615
05.02.201620
10.02.201615

How can I solve this in one table?

Many thx

best regards

Christoph

Tags (1)
1 Solution

Accepted Solutions
sunny_talwar
Not applicable

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

May be this:

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

Where vDate comes from user input

Capture.PNG

3 Replies
sunny_talwar
Not applicable

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

May be this:

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

Where vDate comes from user input

Capture.PNG

chrisg
Not applicable

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

Many Thx! ;Looks good!

sunny_talwar
Not applicable

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

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)