Visokio website     Downloads     Video tutorials     KnowledgeBase  
SUBSET Functions: Calculating sum within a date range - Visokio Forums
SUBSET Functions: Calculating sum within a date range
  • DDrob May 1, 2015 11:26AM
    Hi guys

    I haven't posted here before, I usually manage to work my issues out if I look at it for long enough but this one is just puzzling me. I feel like I'm doing everything right but I'm not getting the desired outcome.

    My data is sales data, split by day and email address, where each row is a sale. I'm trying to create a new field which determines whether the sale was from a new customer (this was their first purchase in the last year), or an existing customer (purchased in the last year).

    My steps were as follows:
    1. Create a new field (StartDate) which is a DATEADD of -1 year to determine the lower end of my existing customer bracket.
    2. Create a new field which is a function of SUBSET_SUM, SUBSET3 where Email=Email,Date>StartDate,Date
    This will give me a sum of the sales where the email has appeared previously within my date range, and I can take 0 values as new and anything else as existing.

    Now, my new field isn't returning anything and I can't work out why.

    Hope you can help.


    P.S. I will attach the formula I used when I get access to my file again, although I'm pretty sure my syntax is correct.
  •        CRead May 1, 2015 12:13PM
    Try this which evaluates to true/false. You can set a limit as well, currently zero:
  • DDrob May 5, 2015 8:46AM
    Thank you CRead. Worked perfectly. This is essentially what I had, but for some reason, it doesn't actually return the sum, but if you add the ">0" at the end, it correctly returns true or false. No idea why but it's working!
  •     paola May 5, 2015 12:57PM
    The formula without ">0" should return the sum, please check your syntax arguments.
    Sample file attached.

  • DDrob May 6, 2015 4:10AM
    You're right. Just me having a slow moment. I hadn't scrolled down in the preview far enough for the logic to be satisfied, so the cells were just blank.

    Thanks for all your help.


It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership