showing all items in sales table

[ilanbaruch]

hi all,

in my model i have a sales table (fact) and items table (dim)

in UI i have a sales table with item description and department as dims and sales as measure.

if user select year or month or a specific department he can see only items sold in that period. i want to present all items

related to that department including these were not sold with 0 as sales amount. 

tried it with adding below expression to the table: 

Count({1} distinct item_id)

but it retrieves all items in the data model disregarding department selection. 

any ideas ?

advanced thanks

[dplr-rn]

this is happening because there is no data there during that period. to show this you may need to generate missing data.

check this

https://community.qlik.com/t5/QlikView-Documents/Generating-Missing-Data-In-QlikView/ta-p/1491394

[ilanbaruch]

isn't there any workaround ?

[Brett_Bleess]

Have a look at the following Design Blog post, believe that may be another option for you:

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

Regards,
Brett

[Kushal_Chawda]

One option is to create the Island table for Item

Table:
Load  *,
      Item
FROM table;

Item_Island:
load distinct Item as Item_new
resident Table;

Now You can Add Item_new as dimesion and create measure like below

Sum(if(Item_new=Item,Sales))