Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi,
See attached file.
Succes!
Halmar
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
Hi,
Can you give me a sample of your problem.
I add a second dimension and nothing changed.
Halmar
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