Sum based on another dimension

[#1]

Hi

I have a problem about an if statement and sum

I have a table where I want to create an expression called "Product with no Sales " with a flag of 1 or 0 if another expression called "Sales" is 0. Then I want to sum "Product with no Sales" on the dimension ProductCategory but based on how many Products in that category that has "Sales" = 0

Example: How many Products have sum(Sales)=0. Then I want to sum "Product with no Sales" on the dimension ProductCategory.

This is how I want to present it

ProductCategory	Sales	Product with no Sales
ProductCategory1	100	2
ProductCategory2	400	1
ProductCategory3	600	3

In this example 2 of the products in ProductCategory1 has Sales=0 and the other products in that category has a total sum of 100

I don't get the expression for "Product with no Sales" to work,  if(sum(Sales)=0, 1, 0) because I want it on the product level

Anyone that has an idea how to do this?

Meddelandet redigerades av: Fredrik Nilsson I have inserted an example file.

[robert_mika]

Could you post your data or file?

[#3]

I have inserted an Example qvw. I want to count the Products that have no Sales for the ProductCategory

[Anonymous]

Check this out. If this is not what you just let me know.

[robert_mika]

Try

count({<Sales={''},ProductCategory>} Sales)