Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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
May be this:
=Sum({<Date= {"$(='>=' & Date(Max(Date) -30, 'DateFieldFormatHere') & '<=' & Date(Max(Date), 'DateFieldFormatHere'))"}>} Quantity)
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))
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)
sometimes max(date)='01/09/2016' the expression will be incorrect
Not sure I understand? Why would it be incorrect?
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
What would be expected output then, if not 2?
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
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