Sum of Products with no sales

[schmidtj]

Hi,

lets say i have a dimension table with product:

product-id

1

2

3

4

then i have a table with sales per product:

product-id	sales
1	15
1	12
2	10
3	18

my goal is now to create a kpi, which gives me the distinct number of products, which have no sales.
in this case the kpi would show 1 (product-id: 4).
i could edit my data script accordingly by creating a grouped table and use that data for this.
however, i would like to have a solution without editing the data script.
i have no idea though how to write the set analysis part to achieve this.

can somebody help me with this?

[agigliotti]

Hi @schmidtj ,

could you post a sample app with mock data to look at?

Best Regards
Andrea

[schmidtj]

hi Andrea,

sorry no i have nothing to give out.
however the example is as simple as described.
i could use that example to aplly it to my problem.

[martinpohl · Accepted solution]

because you can't count null-values do this:

=count(distinct [product-id])-
count ({<[product-id]={"=sum(sales)>0"}>}distinct [product-id])

count all product-id and then subtract those with sales > 0

Regards

[schmidtj]

hi,

easy but effective - great thank you!

quick question for my understanding.
with [product-id]={"=sum(sales)>0"} you use set analysis to get only the products with sales > 0.
does it matter what is used on the left side of the statement then, since there is no comparison?

by the way [product-id]={"=sum(sales)>0"} seems to work fine too 🙂

[martinpohl]

does it matter what is used on the left side of the statement then, since there is no comparison?

you mean does it matter what dimension is used? yes. You want to count product-id, so you have to reduce product-id.

You also could use for example:

count ({<[customer-id]={"=sum(sales)>1000"}>}distinct [product-id])

then you wold count the product-id only from that customers where the sum of sales in total is more than 1000.

Regards