Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

aggr() problem

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?

A

6 Replies
MVP
MVP

aggr() problem

How about DISTINCT instead of NODISTINCT?

Not applicable

Re: aggr() problem

If I use distinct I get no value at all !

A

T: 01732 741414

M: 07711 183919

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

MVP
MVP

Re: aggr() problem

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/

Not applicable

Re: aggr() problem

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.shot.jpg

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

Thanks

A

MVP
MVP

Re: aggr() problem

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

Not applicable

Re: aggr() problem

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.

A

Community Browser