Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
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

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