topic Re: sum aggregate averages in QlikView

sum aggregate averages

Wed, 20 Mar 2013 11:33:18 GMT

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 — Wed, 20 Mar 2013 11:40:08 GMT

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

Wed, 20 Mar 2013 11:44:49 GMT

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

Re: sum aggregate averages

Wed, 20 Mar 2013 12:02:38 GMT

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

Wed, 20 Mar 2013 12:32:33 GMT

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.