4 Replies Latest reply: Mar 20, 2013 9:02 AM by Clarissa Buda RSS

    sum aggregate averages

    Clarissa Buda

      I have a table with columns for:

      Mall

      Tenant

      Month

      Sales

      SquareFootage

      To show sales by month for tenants at different malls.

       

      I've created a bar chart to show total sales per Year.

      I'm also trying to create a chart to show sales per square feet per year. 

       

      I need to average the square footages for each specific tenant over the year, and then take the sum of those square feet.  However, I only want to include tenants who have 12 full months of sales for that year.

       

      The expression I'm using is this:

       

      SUM ( AGGR ( AVG ( IF ( COUNT ( DISTINCT Month ) = 12 , SquareFootage , 0 ) ) , Mall , Tenant )

       

      but it returns no data.

       

      What I am able to get to work is:

       

      SUM ( AGGR ( AVG ( SquareFootage ) , Mall , Tenant ))

       

      which returns the sum of the averages of the square footage for all tenants.  But I'm having trouble adding the conditional so that it only does it if sales exist for 12 full months.

       

      I'm sure if someone can help me figure out how to get the average square footages to work I should be able to get the sales formula to work in order to get my PSF number.

       

      if anyone has any tips for how my formula is wrong?  Or suggestions for a modification to my script?

      Thanks!

        • Re: sum aggregate averages
          Gysbert Wassenaar

          Maybe like this: SUM({<Tenant={"=count(distinct Month)=12"}>} AGGR(AVG(SquareFootage), Mall, Tenant))

          If not, please post a sample document or sample data.

            • Re: sum aggregate averages
              Clarissa Buda

              That does it. Thanks, I didn't think about using set analysis for it.

              • Re: sum aggregate averages
                Clarissa Buda

                Now that worked when I had a single year selected.  but doesn't work when there are multiple years. any thoughts?

                • Re: sum aggregate averages
                  Clarissa Buda

                  i created a sample qvw to try to demonstrate my issue in more detail.  the formula above worked great when only one year was selected.  but i am using set analysis in my charts to show certain years regardless of what is selected.  see attached file, hopefully that helps you help me

                   

                   

                   

                  Update: this isn't in the attached file, but i am getting pretty close by changing the formula to

                   

                  sum({<Tenant={"=aggr(count(distinct MonthYear),Mall,Tenant,Year)=12"}>}

                  aggr(avg(SF),Year,Mall,Tenant))

                   

                   

                  but i think it's requiring that the tenants are there for 12 months for each of the years that i select. 

                  But if I'm selecting 2010, 2011 and 2012, and a tenant has 12 months of sales in 2011 only, then I want to show their avgSF in that year only.  but i think since it doesn't have 12 months in all three of those years, it's excluding their SF completely from all years.