Qlik Community

Ask a Question

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Specialist
Specialist

showing all items in sales table

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

Labels (2)
4 Replies
Partner
Partner

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

Specialist
Specialist

isn't there any workaround ?

Digital Support
Digital Support

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
MVP
MVP

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))