Urgent: Set Analysis Help

[#1]

Hi everyone,

I have a pivot chart in the following format with product, month as dimensions and sales, customers as measures. Please note that the dimension
Product takes the value "Total" and it is not the grand total

                 Month1
Product Sales Customers
A  100
B  200
C  300  1
D  400
E  500  1
Total  1500  2

      
When i use set analysis to get the customers for Product "Total" by using the expression

Avg. Sales = sum(Sales)/sum({$<[Product] = {'Total'}>}Customers)

i get the avg. sales populated only for one row which is for the product "Total" row, but it is not showing the values for all the products,
not sure why this is happening.

Month1
Product  Sales Customers Avg. Sales
A  100        -
B  200        -
C  300  1    -
D  400        -
E  500  1    -
Total  1500  2 750

Ideally i would like to have the output to be

       Month1
Product  Sales Customers Avg. Sales
A  100   50
B  200   100
C  300  1 150
D  400   200
E  500  1 250
Total  1500  2 750

Your feedback is really appreciated

Tkx
-Harsha

[#2]

Hi,

See attached file.

Succes!

Halmar

[#3]

Hi Halmar,

Thank you for your response.

The solution you have provided works perfectly for one dimension (although i am not sure why it says error in expression).

In my case i have two dimensions, once i use this, the total customers is getting added across all the values of the second dimension so the number of customers are getting inflated.

Any ideas of getting around this?

Tkx.

Harsha

[#4]

Hi,

Can you give me a sample of your problem.

I add a second dimension and nothing changed.

Halmar

[#5]

Hi Halmar,

Please find attached the sample qlikview file.

In the qlikview file, in month 01, ideally when i calculate the Avg. Sales the sales value should have been divided by the customers for product "Total" in that month, but it takes the customers across all months. Not sure why this is happening.

Any ideas?

Thanks
-Harsha