0 Replies Latest reply: Jul 21, 2014 10:25 AM by Steve Lord RSS

    Expresion help, divide vGoal by number of days in line chart expression

    Steve Lord

      Hi, I have this really nice line chart that works well and has this expression for whether people met a daily goal to make a nice 'Goal Met' line.

       

      count(distinct if(aggr(sum(Steps), UserId, [Steps Date])>=vStepsDailyGoal, UserId))

       

      It has [Steps Date] as its only dimension, another line with <vStepsDailyGoal not met, and another line totaling the two.

       

      I have a pie chart beside the line chart that relies on a vStepsGoal expression, and after a little operator feedback I decided to modify the above expression to rely on vStepGoal so people would only have to input the one goal.  I tried variations of this to no avail:

       

      count(distinct if(aggr(sum(Steps), UserId, [Steps Date])>=Div(vStepGoal, RangeCount(Distinct Date(Floor([Steps Date])))), UserId))

       

      If I try just Count(), or use / instead of Div() I get expression error messages.  The RangeCount() pulled me out of expression error messages, but flat lines the goal met line at 0 for all days.  I had 7 days selected, 7000 in the vStepsDailyGoal and 49000 in the vStepsGoal, so it should work out numerically.

       

      I get the desired line for a week when I do this:

      count(distinct if(aggr(sum(Steps), UserId, [Steps Date])>=Div(vStepsGoal, 7), UserId))

       

      But people might have occasion to select 8 days or 6 days and I'm trying to accommodate this by just counting the days ([Steps Date]) they've selected.  I know I could probably make a variable input box for them to enter the number of days, but my objective is to cut from 2 input boxes to 1, not replace one with another.

       

      Any help would be appreciated.  Thanks!