Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
jaweher89
Contributor III
Contributor III

track of purchasing quantity

I have the following table sales

ItemCode    Date                type            Quantity

AAAAA       01/03/2015      Purchase     50

AAAAA       09/03/2015      Sales           10

AAAAA       15/03/2015      Sales           20

AAAAA       15/04/2016      Sales           2

when Type = purchase , it's the first purchase of the item and when type=sales it's an action of sales of the item

how to have an expression in table in order to have a track of quantity fo the first 30 days after the first purchase ?

what I try but it's wrong How to correct it

=sum({<Date= {"$(='>=' & Date') & '<=' & Date+ 30'))"}>}Quantity)

1 Solution

Accepted Solutions
sunny_talwar

I got it, try this:

=Sum({<Date= {"$(='>=' & Date(Max({<type = {'Purchase'}>}Date), 'DateFieldFormatHere') & '<=' & Date(Max({<type = {'Purchase'}>}Date) + 30, 'DateFieldFormatHere'))"}, type = {'Sales'}>}>} Quantity)

UPDATE: This should give you a date range of 01/03/2016 - 30/03/2016

View solution in original post

9 Replies
sunny_talwar

May be this:

=Sum({<Date= {"$(='>=' & Date(Max(Date) -30, 'DateFieldFormatHere') & '<=' & Date(Max(Date), 'DateFieldFormatHere'))"}>} Quantity)

sunny_talwar

Or this:

=RangeSum(Sum({<Date= {"$(='>=' & Date(Max(Date) -30, 'DateFieldFormatHere') & '<=' & Date(Max(Date), 'DateFieldFormatHere'))"}, type = {'Purchase'}>} Quantity), -Sum({<Date= {"$(='>=' & Date(Max(Date) -30, 'DateFieldFormatHere') & '<=' & Date(Max(Date), 'DateFieldFormatHere'))"}, type = {'Sales'}>} Quantity))

MK_QSL
MVP
MVP

Something like below?

Data:

Load * Inline

[

  ItemCode,    Date,                type,       Quantity

  AAAAA,       01/03/2015,      Purchase,        50

  AAAAA,       09/03/2015,      Sales,           10

  AAAAA,       15/03/2015,      Sales,           20

  AAAAA,       25/04/2015,      Sales,           20

  BBBBB,       01/03/2015,      Purchase,        100

  BBBBB,       12/03/2015,      Sales,           10

  BBBBB,       24/03/2015,      Sales,           25

  BBBBB,       25/03/2015,      Sales,           30

  BBBBB,       25/04/2015,      Sales,           10

];

Left Join (Data)

Load ItemCode, FirstSortedValue(Date, Date) as MinPurDate Resident Data

Where type = 'Purchase'

Group By ItemCode;

Left Join (Data)

Load

  ItemCode,

  Date,

  type,

  Quantity,

  MinPurDate,

  If(Date-MinPurDate <= 30, 1, 0) as Flag

Resident Data

Where type = 'Sales';

Now use below expression

=SUM({<Flag = {1}>}Quantity)

jaweher89
Contributor III
Contributor III
Author

sometimes max(date)='01/09/2016' the expression will be incorrect

sunny_talwar

Not sure I understand? Why would it be incorrect?

jaweher89
Contributor III
Contributor III
Author

I update my question , I would like to get the first 30 days of sales . In the example I will get 2 which is wrong

sunny_talwar

What would be expected output then, if not 2?

sunny_talwar

I got it, try this:

=Sum({<Date= {"$(='>=' & Date(Max({<type = {'Purchase'}>}Date), 'DateFieldFormatHere') & '<=' & Date(Max({<type = {'Purchase'}>}Date) + 30, 'DateFieldFormatHere'))"}, type = {'Sales'}>}>} Quantity)

UPDATE: This should give you a date range of 01/03/2016 - 30/03/2016

View solution in original post

sunny_talwar

I think this is a much better way to do it, because as Manish pointed out, the Purchase date may vary for each ItemCode and set analysis will be static. Creating a flag in the script will make your life so much more easier