topic Re: aggr() problem in QlikView

aggr() problem

Thu, 30 Jun 2011 21:03:40 GMT

Hi - I have a calculated dimension in a pivot table which is summarising percentages across multiple records as follows:

num(aggr(nodistinct avg(People.TargetRatio/100),EndDate),'##%')

However - it seems to generate 2 records for some of the data I thought that the nodistinct would get rid of this - cany anybody explain why this might be so?

aggr() problem

johnw — Fri, 01 Jul 2011 00:04:37 GMT

How about DISTINCT instead of NODISTINCT?

Re: aggr() problem

Sun, 03 Jul 2011 16:07:56 GMT

If I use distinct I get no value at all !

T: 01732 741414

M: 07711 183919

E: amanda@azaralogic.com<mailto:amanda@azaralogic.com

Re: aggr() problem

stevedark — Sun, 03 Jul 2011 21:34:44 GMT

Hi there - I presume when you say two records for some data that you mean for the same date? If so are you sure that there are no time values included on the date field?

You can check this by creating a new list box with EndDate in it. If there are times included on the dates then two rows will be displayed for that date - even though the time is not displayed.

If this is the case you need to wrap EndDate with a DayStart function on load, eg.:

Date(DayStart(EndDate), 'DD/MM/YYYY') as EndDate,

If this isn't the problem then please post back, and we can try another tack. Incidentally I don't believe you should need any kind of DISTINCT statement in your Aggr.

Steve

QlikView Consultant

http://www.quickintelligence.co.uk/

Re: aggr() problem

Wed, 20 Jul 2011 20:50:20 GMT

Hi Steve

Sorry for the delay in posting back and thanks for your assistance.

Please see image for what I get - I would expect only a single value in the Target Ratio, and all the other columns.

It should be combining two ratios 45% and 78% should be 61.5%.... what might cause multiple entries in this column?

Thanks

Re: aggr() problem

stevedark — Wed, 20 Jul 2011 20:56:18 GMT

You need to put the Target Ratio as expressions, rather than dimensions in order to combine them. Be very careful how you combine the percentages though - as just adding them together and dividing by two will give a duff value. Can you go back to how the targets are derived and work them out in the expression?

- Steve

Re: aggr() problem

Wed, 20 Jul 2011 21:12:40 GMT

Hi Steve

My report is a Pivot - if I put the Target Ratio as an expressin it just repeats the figure as a sub-column which is not what I want. I think it must be something in the raw data that is causing it.... I'm going to try that. I take your point about how to work out the % though.... agreed.