6 Replies Latest reply: Jul 20, 2011 5:12 PM by azaralogic RSS

    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

        • aggr() problem
          John Witherspoon

          How about DISTINCT instead of NODISTINCT?

          • Re: aggr() problem
            Steve Dark

            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

                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

                  • Re: aggr() problem
                    Steve Dark

                    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

                        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