LAE

Welcome to the LAE community!  Please feel free to start a discussion in the discussion tab or join in a conversation.

Discussions

Members

Resources

Events

 View Only

  • 1.  Max Purchase Date w Null Values

    Employee
    Posted 05-05-2015 12:27

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: mgp

    I have a list of customers with the maximum purchase date by month (Calculated in agg ex using groupMax)
    In months where there aren't any purchases, I receive a value of null.
    For those months where there is a null, I would like to return the most recent purchase for that customer - regardless of whether it occurred in the month or not.

    Please find an example of the desired output attached. I am trying to calculate this for a fairly large data set. Thank you for any help you can provide.

    Capture.PNG


  • 2.  RE: Max Purchase Date w Null Values

    Employee
    Posted 05-05-2015 21:51

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: ryeh

    I'm guessing it's you, Marisa? Good to see you on the forums!

    I assume that you used Agg Ex to generate the first three columns in that screen capture - Customer, Period, and Max Purchase Date. If so, then the attached graph should work for you. I basically use a filter to keep track of the latest Max Date.

    Does that work for you?

    (Oh, and I also assume that the records are already sorted by Period.)
    Attachments:
    maxDate.brg


  • 3.  RE: Max Purchase Date w Null Values

    Employee
    Posted 05-06-2015 07:14

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: mgp

    Hi Roger! Yes, it's Marisa. Thanks for your help!
    This works great only that I need it to reset for each customer. Right now, when the customer changes, it is returning the max date for the last entry related to the prior customer.


  • 4.  RE: Max Purchase Date w Null Values

    Employee
    Posted 05-06-2015 07:39

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: ryeh

    Oh, right, of course. My mistake. See the attached. (You can copy and paste the text in the Code box directly onto a graph.)

    node:Max_Date_Per_Customer_ID
bretype:core::Agg Ex
editor:Label=Max Date Per Customer ID
editor:sortkey=554a27ad6e8f76da
input:@4b4668c040aa5a85/=Static_Data.40fe6c55598828e5
output:@4b4668e708143fb4/=
prop:GroupBy=<<EOX
CustID
EOX
prop:Script=<<EOX
if firstInGroup
then MaxDate = 'Max Purchase Date'

if 'Max Purchase Date'.isNotNull()
then MaxDate = 'Max Purchase Date'

emit *, MaxDate as "New Max Purchase Date"

EOX
editor:XY=230,230
node:Bypass
bretype:::Bypass
editor:shadow=4b467f7e02db3a85
input:@4b467f7e129d45c1/=
input:@4b467f830ffe047b/=
output:@40fd2c7436717256/=
end:Bypass

node:Sort
bretype:::Sort
editor:shadow=4b467f8972dc33df
input:@40fd2c743ebf4304/=
output:@40fd2c746a2a3b47/=
end:Sort

node:Agg
bretype:::Agg
editor:shadow=4b467f9b3d5028c0
input:@40fd2c7427456e5b/=
output:@40fd2c744c862db0/=
end:Agg

end:Max_Date_Per_Customer_ID

node:Static_Data
bretype:core::Static Data
editor:sortkey=55499c0579a66d24
output:@40fe6c55598828e5/=
prop:StaticData=<<EOX
CustID,Period,Max Purchase Date:date
123,Jan 2013,2013-01-27
123,Feb 2013,NULL
123,Mar 2013,NULL
123,Apr 2013,2013-04-05
123,May 2013,NULL
1234,Jan 2013,NULL
1234,Feb 2013,2013-02-27
1234,Mar 2013,NULL
1234,Apr 2013,2013-04-05
1234,May 2013,NULL
12345,Jan 2013,2013-01-27
12345,Feb 2013,NULL
12345,Mar 2013,NULL
12345,Apr 2013,2013-04-05
12345,May 2013,NULL
EOX
editor:XY=70,130
end:Static_Data

node:Get_Max_Date
bretype:core::Filter
editor:Label=Get Max Date
editor:sortkey=55499c7b35db2a54
input:@40fd2c74167f1ca2/=Static_Data.40fe6c55598828e5
output:@40fd2c7420761db6/=
prop:Script=<<EOX
if firstExec
then MaxDate = 'Max Purchase Date'

if 'Max Purchase Date'.isNotNull()
then MaxDate = 'Max Purchase Date'

emit *, MaxDate as "New Max Purchase Date"

EOX
editor:XY=230,130
end:Get_Max_Date


  • 5.  RE: Max Purchase Date w Null Values

    Employee
    Posted 05-06-2015 07:50

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: mgp

    This worked perfectly! Thank you so much for the help


  • 6.  RE: Max Purchase Date w Null Values

    Employee
    Posted 05-06-2015 14:23

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: ryeh

    Any time! Keep the questions coming!