• Replies 9 replies
  • Subscribers 962 subscribers
  • Views 478 views
  • Users 0 members are here
Related

New subscribers widget

Anh Le
$organization
Hi everyone,
 
I’m sure there is a simple way to do this that my brain just isn’t thinking of at the moment… If I want to build an indicator widget that shows how many of my current season subscribers are new subscribers (but not necessarily new to file), has anyone done that and might have recommendations?
 
Thanks for your help!
Anh
 
signature_1078652768
Anh Le (she/hers/hers)
Director of Marketing & PR
OPERA THEATRE OF SAINT LOUIS
email: ale@opera-stl.org
phone: 314.963.4294 | mobile: 760.834.5926
210 Hazel Ave St. Louis, MO 63119
facebook icon twitter icon youtube icon linkedin icon instagram icon 
 
Parents
  • $organization

    Hello ,
    I know it has been a while since your initial post, but I'm curious if you ever got Chris W.' formula to work for this question? New constituents that are in a package for the first time?

    When I tried the formula Chris mentioned, I received 0 for my count.  The only filter I have in both dashboard and widget is season fiscal current year = 0. 
    Also, I limited the widget to just look at the ISNULL statement as you thought at might be sufficient enough. (I did try at first with the added "OR" statement as well, but no luck there as well. 
    This is what my formula looks like:



    I can post my dash if anyone wants to try.

    Thanks!
Reply
  • $organization

    Hello ,
    I know it has been a while since your initial post, but I'm curious if you ever got Chris W.' formula to work for this question? New constituents that are in a package for the first time?

    When I tried the formula Chris mentioned, I received 0 for my count.  The only filter I have in both dashboard and widget is season fiscal current year = 0. 
    Also, I limited the widget to just look at the ISNULL statement as you thought at might be sufficient enough. (I did try at first with the added "OR" statement as well, but no luck there as well. 
    This is what my formula looks like:



    I can post my dash if anyone wants to try.

    Thanks!
Children
  • $organization in reply to Michael Moore

    Hi Michael,

    The formula in that image is incomplete from the one I posted above, so might try including the second half of the IF condition... and confirm that there a filter to limit the results to only packages.

  • $organization in reply to Chris Wallingford

    Hi , I went ahead and added the rest of that formula but didn't receive any counts.  
    I thought best to share the.dash and see if something looks off on your end in that formula.

    Here is that formula without showing the applied filters in the season current fiscal year offset:

    SUM ([Constituent ID],
    IF (
    ISNULL (([# of unique Package Seat Key],[Season Fiscal Current Year Offset]))

    OR
    ([# of unique Package Seat Key], [Season Fiscal Current Year Offset])=0
    ,1
    , NULL
    )
    )

    NewSubscribers.dash


    Thanks
    Mike

  • $organization in reply to Michael Moore

    I see now you're grouped by Season. I'd swap that our with Season Type, or add ALL([Season]) to both parts of the IF, but if there are multiple season rows the latter will count a past purchase to any Season Type as a prior purchase making them not new to the Season of the given row.

    That said, the formula is still giving me trouble. I tried Returning (in screenshot below) and it gave me results...

    SUM([Constituent ID],
     IF(
     ([# of unique Season],[Season Fiscal Current Year Offset]) > 0
     ,1,0
     )
     )

    But when I reverse the ,1,0 to ,0,1 I get all zeros. I'll dig into later, but in the meantime, the Venn approach may be better in this case anyway.

    ([# of unique Constituent ID],[Season Fiscal Current Year Offset <= 0])
    - ([# of unique Constituent ID],[Season Fiscal Current Year Offset < 0)

    For New subscribers, this the unique count of all subscribers in this and any prior year minus the count of all subscribers in any prior year.