track of purchasing quantity

[Anonymous]

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)

[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]

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)

[Anonymous]

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

[sunny_talwar]

Not sure I understand? Why would it be incorrect?

[Anonymous]

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 · Accepted solution]

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