• Replies 8 replies
  • Subscribers 701 subscribers
  • Views 263 views
  • Users 0 members are here
Options
Related

Hard-coded goals and formulas

Odele Peter
$organization

Hi - following up on today's topic about hard-coding goals in a widget. We recently had a package that was set up as one event and it had separate goals for each price zone group. I created a widget that summarized sales by price zone group and needed to hard-code package # goals and % rev to sales goal. The key was to find something that distinguishes each row - in this case, the zone group rank. I will say ... I wouldn't share this widget with staff since the hard-coding is hidden in the formulas and therefore results are inaccurate when widget/dashboard filters are changed. But I did share it as a report to staff. 

The goal uses the rank of each zone group to distinguish each row:

And the % to rev goal is similarly:

Odele

  • $organization

    Oh interesting.  I haven't seen this done before and will have to try it out.  How are you getting the % to rev goal to show as a bar?

  • $organization in reply to Kanani Reichlin

    Data bars are a Values feature in the hamburger dropdown

  • $organization in reply to Heath Wilder

    Ooh thanks, Heath!  p.s. I just found your availability dash in the forums and have been fixing it up for our uses for dynamic pricing decisions.  Thanks for sharing (3 years ago)

  • $organization in reply to Kanani Reichlin

    OH great.  Let me know how you go.  It's an early effort and I'd love to find out about your upgrades.

  • $organization

    I was able to use this, modify it, and look at max allocations for certain zones, weekdays vs weekends:

    CASE

    WHEN( [Max Zone Group Rank]) = 7 AND ([Max Day Of Week]) = 1 THEN 920
    WHEN( [Max Zone Group Rank]) = 7 AND ([Max Day Of Week]) = 2 THEN 755
    WHEN( [Max Zone Group Rank]) = 7 AND ([Max Day Of Week]) = 3 THEN 755
    WHEN( [Max Zone Group Rank]) = 7 AND ([Max Day Of Week]) = 4 THEN 755
    WHEN( [Max Zone Group Rank]) = 7 AND ([Max Day Of Week]) = 5 THEN 755
    WHEN( [Max Zone Group Rank]) = 7 AND ([Max Day Of Week]) = 6 THEN 755
    WHEN( [Max Zone Group Rank]) = 7 AND ([Max Day Of Week]) = 7 THEN 920

    WHEN( [Max Zone Group Rank]) = 8 AND ([Max Day Of Week]) = 1 THEN 1840
    WHEN( [Max Zone Group Rank]) = 8 AND ([Max Day Of Week]) = 2 THEN 1580
    WHEN( [Max Zone Group Rank]) = 8 AND ([Max Day Of Week]) = 3 THEN 1580
    WHEN( [Max Zone Group Rank]) = 8 AND ([Max Day Of Week]) = 4 THEN 1580
    WHEN( [Max Zone Group Rank]) = 8 AND ([Max Day Of Week]) = 5 THEN 1580
    WHEN( [Max Zone Group Rank]) = 8 AND ([Max Day Of Week]) = 6 THEN 1580
    WHEN( [Max Zone Group Rank]) = 8 AND ([Max Day Of Week]) = 7 THEN 1840

    END

    Basically, weekENDs have different allocations (in this case General Admission tickets) than weekDAYs, and the 3-4pm zone is different than the 4-6pm zone.

  • $organization

    Hi Odele,

    Thanks for posting this.  I finally got a chance to look at this in analytics.  I was trying to replicate your formulas in the Plans cube with Plan Type, in place of the Zone Group Rank.  However, I kept getting an error and couldn't get it to work, even with a few variations to the formulas.  I was wondering whether case statements only work with fields that have numeric values. 

    Does anyone have any suggestions of doing something similar hard coding goals based on Plan Type?

    Thanks.

    T.C.

  • $organization in reply to T.C. Brown

    Hi T.C.,

    Yes, conditionals do require numeric expression tests. So try turning your condition into a numeric expression that will be unique for the Plan Type...

      

    CASE WHEN MAX([Plan ID]) = ( MAX([Plan ID]) , [Plan Type = Annual Prospect] ) THEN 400000
    ELSE 0 END

    When the maximum Plan ID for this row, is the same as the maximum Plan ID for the Plan Type = Annual Prospect row, then show 400,000...

  • $organization in reply to Chris Wallingford

    Hi Chris,

    I tried this and it works perfectly!

    Thanks so much.

    T.C.