Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

stevelord
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