# 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
Did you mean:
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
MVP

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

9 Replies
MVP

May be this:

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

MVP

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))

MVP

Something like below?

Data:

[

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)

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)

Contributor III
Author

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

MVP

Not sure I understand? Why would it be incorrect?

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

MVP

What would be expected output then, if not 2?

MVP

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

MVP

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

Community Browser