Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Valued Contributor

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

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!

Community Browser