topic Re: Urgent: Set Analysis Help in QlikView

Urgent: Set Analysis Help

Sat, 22 Oct 2011 14:49:47 GMT

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

Re: Urgent: Set Analysis Help

Mon, 24 Oct 2011 06:55:29 GMT

Hi,

See attached file.

Succes!

Halmar

Urgent: Set Analysis Help

Mon, 24 Oct 2011 15:50:43 GMT

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

Re: Urgent: Set Analysis Help

Tue, 25 Oct 2011 09:41:49 GMT

Hi,

Can you give me a sample of your problem.

I add a second dimension and nothing changed.

Halmar

Re: Urgent: Set Analysis Help

Thu, 27 Oct 2011 04:49:16 GMT

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