Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I'm a new qlik sense user and I've been racking my brain for hours without getting anywhere.
I have a table with sales splitted by product and cities.
I need to sum sales of all cities where Cluster Special' Products were sold (in the example, I have Product A in special cluster)
So, in the table below, product A was sold in city1 and city3. I need to understand the percentage of total sales these 2 cities represents, regardless of the product.
Once identified the cities , I will sum all sales (regardless of the product or cluster) of them (city1 +city3) and then divide it by total sales to find out how representative they are.
The problem is that I'm not being able to create a condition that sums the sales of the cities selected regardless of the product. It always considers only sales of special products in the cities. I need it all. Cluster field should only help me to identify which cities I should sum.
One of my many attempts of calculation was:
If(CLUSTER='Special' and Sales>0,
sum({<[ANO]={$(=Max([ANO]))},>}$(=[Sales]))
,0)
City | Product | Cluster | Sales |
City1 | A | Special | 75 |
City2 | A | Special | 0 |
City3 | A | Special | 41 |
City1 | B | Other | 23 |
City2 | B | Other | 97 |
City3 | B | Other | 86 |
City1 | C | Other | 99 |
City2 | C | Other | 84 |
City3 | C | Other | 68 |
Total sales | 573 | ||
City1+City3 | 392 | ||
%represent | 68,4% |
Can someone help me, please?
If(CLUSTER='Special' and Sales>0,
sum({<[ANO]={$(=Max([ANO]))},>}[Sales])
)
/
sum({<[ANO]={$(=Max([ANO]))},>}[Sales])
Tks, Channa
But this syntax only considers sales from cluster special. I was hoping to sum all sales of all products of cities that have sales of special cluster
The cluster information should be used only yo id the cities I have to sum. The sum itself should consider all products sold in the city
I think it's only with an indirect set analysis lie p() and e() possible to set the conditions. Take this as a starting point for your task:
- Marcus