expression help for pie chart

[stevelord]

Hi I have a straight table like this:

Dimension=UserId

Label= UserID

Expression=Sum(steps)

Label=Steps

Expression==if(sum(Steps)>=vStepsGoal, count(Distinct UserId))

Label=Met goal

UserId    Steps       Met Goal

               100000    2

ABC       52000        1

123         48000        1

And when I enter 50000 in the vStepsGoal inputbox, the above collapses to show and count just the user who met the goal.

Now on my pie chart I have this:

Dimension=None (I intend to make 3 expressions counting people by met goal, goal not met, and logged 0)

Expression=if(aggr(sum(Steps), UserId)>=vStepsGoal, count(Distinct UserId))

Label=Met goal

And the result should be a pie chart that updates the count for this expression to reflect # who met goal.  Instead it shows everyone until I set the goal up above what everyone logged combined (100001+ in this example), at which point it shows no one.

I'm trying to get it to sum the steps per individual, then count the # for whom that sum >= than the goal.  Help please, thanks!

(I know it will just be a circle right now, but I need the count to come out right when I hover over it.  And if I get the right formula for >= goal. I will add a couple more for >= AND <goal, and =0 to make it a 3-slice pie chart by force )

[stevelord]

**edit to last sentence of original post**

(I know it will just be a circle right now, but I need the count to come out right when I hover over it.  And if I get the right formula for >= goal. I will add a couple more for >0 AND <goal, and one for =0 to make it a 3-slice pie chart by force )

[maxgro · Accepted solution]

perhaps

count(distinct if(aggr(sum(Steps), UserId)>=vStepsGoal, UserId))

[stevelord]

That did the trick.  If anything can be said to be awesome and stink at the same time, it's Qlikview.

'if goal is met, count the people' didn't work, but 'count if goal is met, the people' did work.

Is there a country with grammar rules like the latter syntax?  Maybe I could learn that language and make qlikview work better?   I studied German for some years and suppose qlikview could be worse if they arbitrarily made some formulas male and others female.

...actually, I think I understand the turn of logic now.

[MarcoWedel]

just for fun another one:

=Sum(-Aggr(Sum(Steps)>=vStepsGoal, UserId))

I agree that adding some of our language specialties could have improved the QlikView syntax.

e.g. all function names would be at least three times as long and the simple and self explaining set expression syntax probably would include characters like ä,ö,ü or ß

greetings from Germany

Marco