Show names of products with no sales in the given (month, year, week or day)

[Brownie1]

hey there, this is how the data is being shown to me. My Question is that i need to show the products which have had no sales in given or particular selected month.  I am unable to find a suitable expression.  for instance i select the month january. soo all the products having no sales must be shown to be in the table or what so ever visualization.  and same is the case with Quarterly year and others 

[Or]

Only({1< ProductName = e(ProductName) >} 'Not Sold') perhaps?

Note that ProductName is assumed to be the dimension in the table and this would be the measure.

Within the set, 1 indicates the entire dataset (ignoring selections and dimensions) and e(ProductName) indicates only products which are currently excluded (that is, not within the current selection).

[Brownie1]

i want the names of the products whose sales are 0.  Not Sold does not hold for me. 

[PrashantSangle]

When you say Sales are 0. Does it mean in your database you have sales value as 0 for those product?

Can you explain with Sample data with expected output?

[Prem0212]

I think this expression will work for u

Sum({<Sales={0}>}Sales)

Please like and accept the solution if u find an answer.

[Gabbar]

add this in set analysis if you are already using one:-
ProductName = {"=(Sum(Sales)=0)"}

or 

you can use this as set analysis:-

Count({<ProductName = {"=(Sum(Sales)=0)"}>}ProductName)

[Rohan]

Hi,

try this,

if(Aggr(Sum({<OrderDate={">=$(=min(OrderDate))<=$(=max(OrderDate))"}>}Sales),Product_Name,OrderDate)=0,Product_Name,null())

Exclude null from the column.

Thanks & Regards,

Rohan.

[SBN]

Hi there,

I might be completely wrong with this, but as far as I know it's not possible to visualize any data that doesn't exist in your dataset.
So if there aren't any records available in your dataset that state that a product has 0 value of sales in a given period Qlik won't be able to show you.
Other than the "gray" selection options.
In these cases you need to create "missing" records in your load script with 0 value of sales for products that weren't sold in that period.
There used to be a good article on the community with an attachment that described how to create missing records in QlikView. But this was archived and not available anymore.
This can also be applied to Qlik Sense.
I've added this PDF to an answer in another thread: Show value when there are no values in the month - Qlik Community - 2059478

If there's anyone out there who knows how to manage this without generating additional records please let us know...😉

Kind regards,

SBN

[Brownie1]

Yes, you are right this is what I was trying to do i.e to bring 0 value against my missing sales against so that i can easily count the products. but then i used this expression and this somehow works 

if(max(OrderDate) < '$(=Date(max(OrderDate)-7,'MM/DD/YYYY'))',sum(Sales))

also, where we have hard coded the date to 7 day prior we can store this in a variable and change it through a slider. 

[Gabbar]

it is possible to bring products who do not have sales data in a particular period but exist in the base without adding zero to their sales.

For Starter you can exclude the product whose sales are greater than 0 for the selected period using this:-

Count({1<ProductName=e({<Sales={">0"}>})>}ProductName)
This expression will work after selecting a period because it will remove all the product name that got a sale in the period. I have added 1 because if the product doesnt have sales, it also wouldnt have sales_Date so you need the external equation free for time fields.

The second case is what I have shared above:
Count({1<ProductName = {"=(Sum(Sales)=0)"}>}ProductName)

i have particularly used them myself in my projects.
If this doesn't solves your query, please attach a sample data.