topic Re: expression help for pie chart in QlikView

expression help for pie chart

stevelord — Tue, 20 May 2014 19:33:23 GMT

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 )

Re: expression help for pie chart

stevelord — Tue, 20 May 2014 19:39:21 GMT

**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 )

Re: expression help for pie chart

maxgro — Tue, 20 May 2014 19:51:07 GMT

perhaps

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

Re: expression help for pie chart

stevelord — Tue, 20 May 2014 20:21:05 GMT

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.

Re: Re: expression help for pie chart

MarcoWedel — Tue, 20 May 2014 23:04:40 GMT

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