Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
isn't there any workaround ?
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
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))