1 Reply Latest reply: May 12, 2011 10:27 AM by Trent Jones RSS

    Aggregation Function in charts

      Can we use aggregation function once we have while making charts and tables.

       

      E.g. I want to aggregate data with respect to month. but don't want to include month as dimension.

       

       

      Regards

        • Aggregation Function in charts

          Not 100% sure I understand exactly what you want but here are some examples of what you can do.

           

          Set analysis:

          =sum({$<[Month]={'January'}>}[Sales])

          =sum({$<[Month]={'February'}>}[Sales])

          etc.

           

          aggr Function:

           

          Let's say you want to sum the invoices for customers that have been with the company less than 180 days (let's also assume you don't have a [Length with Company] field or anything like that) but you only want to show the dimension [City].  You could use the following expression:

           

          =sum

          )

               aggr

               (

                    if(today()-[Join Date]<180,

                              sum([Invoice Total])

                    )

               ,[Account Number]

               )

          )

           

          In the above example you can't use set analysis because the [Join Date] of every customer is going to be different and the set analysis is calculated once per chart, not once per row.  Also use the dimension [City] for the example above.  You're quesiton was pretty vague but hopefully that helped a little bit.