topic Error in Calculated Dimension in QlikView

Error in Calculated Dimension

Fri, 24 May 2013 13:12:00 GMT

Hello,

I'm trying to create a pie chart using a calculated dimension to bucket calculated results. More specifically, I'm using a percentage difference caluclation to make quality evaluations, and I would like to show how many instances fall within each category.

Ideally, I'd write my dimension equation like this:

=if( (Avg(x) - Avg(y)) / Avg(x) < -0.25, 'Bad',

if( (Avg(x) - Avg(y)) / Avg(x) > 0.25, 'Good', 'OK')

)

Then my expression [Count(Key)] would simply distribute the instances across my calculated categories. When I try this out, however, I get // Error in calculated dimension

I've read across the internet that I need to use the aggr() function to accomplish this, but I can't seem to figure out how to implement it in this situation. Any examples I've looked at don't seem to fit my situation. There are around 15 different fields on my page the user can select to change the values of x and y, and those should all be reflected in the equation above. So is aggr() right for me? Do I need to list all those fields in the aggr() function? Am I even barking up the right tree?

Thanks in advance!

Re: Error in Calculated Dimension

Gysbert_Wassenaar — Fri, 24 May 2013 13:23:52 GMT

I think you're right that you need the aggr function. Without it you get only one number back from avg(x). I don't know what should go in your buckets. It could be products or salesmen or months etc. Try starting with Key and see if that gives you the result you need.

=aggr(if( (Avg(x) - Avg(y)) / Avg(x) < -0.25, 'Bad',

if( (Avg(x) - Avg(y)) / Avg(x) > 0.25, 'Good', 'OK')

),Key)

Selections in other fields will still influence which x and y values are used in the calculation..

Re: Error in Calculated Dimension

Fri, 24 May 2013 13:39:49 GMT

Thanks for the quick response. Turns out that I wasn't thinking about the equation very Qlikview-y. Avg(x) is the average price of each product, so aggr(Avg([Price]),[Product]) returned the results I'm looking for.

Even though that returns the proper number, the chart still isn't what I need... It seems that my expression Count(Key) is placing the same number of instances within each bucket. In other words, the categories 'Good', 'Bad', and 'OK' all have 100 items in them, which is equal to the total population.

Is there a trick to dispersing these within each bucket? I assumed that just counting the total in the expression would split them by my category.