topic Re: Sum based on another dimension in QlikView

Sum based on another dimension

Thu, 22 Jan 2015 12:26:47 GMT

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.

Re: Sum based on another dimension

robert_mika — Thu, 22 Jan 2015 12:34:12 GMT

Could you post your data or file?

Re: Sum based on another dimension

Thu, 22 Jan 2015 13:33:12 GMT

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

Re: Sum based on another dimension

Anonymous — Thu, 22 Jan 2015 13:39:24 GMT

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

Re: Sum based on another dimension

robert_mika — Thu, 22 Jan 2015 13:51:13 GMT

Try

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