Displaying unsold products without sales after a filter has been applied

[SBurford]

Edit: I managed to solve my problem using my solution and moving my key for Section Access from the "Sales table"  to the "Product table", based on the suggestion by @teiswamsler , thanks everyone for your help.

Hello, I posted a similar question two weeks ago but I ended up realizing that the answer I got wasn't useful for my particular example, so I had to rephrase it.

I have a set of predefined tables: Invoices and Products. In the invoice table I have the data corresponding to a typical sales table: date, SKU, total, client, vendor and branch office, and on the product table I have SKU, Category and Special Product. This last column is important for later and is a string which is "yes" or "no". 

Also, I defined section access, that limits users access according to their "branch office".

What I want to do:

• Create a table where only products belonging to "Special Category" are displayed. If they haven't been sold, 0 should be displayed. For instance, if there is a total of 100 products belonging to Special Category and a user from branch office X wants to check this table, there should be a total of 100 products in that table, even if he has restricted access. 

What  I have done and problems I have found:

• Data model that on one hand shows a sales table by date, SKU, total, client, vendor and branch office, and on the other hand displays a product table by SKU, Category and Special Product. When I see the published app as a restricted user, I can only see displayed on the table products that are a Special Product that were sold in the particular branch office, even though the data is associated to the products table (I'd expect to see null entries for those products). This means that if I have a total of 100 products, but only 30 were sold there, I'd see maximum data for 30 products.  For instance, this would show me 100 products with full access but only 30 on restricted access:

Count({<[Special Product]={"yes"},SKU=,[Branch Office]=,[Vendor] =,Client=,Category=>} TOTAL DISTINCT SKU)

•  I was "careful enough" to define the Special Product category on the products table. This is because I thought that products that wouldn't be sold would still have that category associated, hence allowing me to retrieve the SKU and Special Category without sales. This clearly hasn't worked out
• I defined each branch office according to the following model:

Section Access;

LOAD * INLINE [
ACCESS,USERID,BRANCH_OFFICE
USER,COMPANY\sburford,*
USER,COMPANY\user1,X
USER,COMPANY\user2,Y
ADMIN,INTERNAL\SA_SCHEDULER,*
];

Section Application; [...]

Regarding this last aspect, maybe I could define something additional on the Section Access to achieve my desired results? Should I add something to the data model?

Expected result: Note that this should work regardless the filter I apply on data (date, client, vendor, branch office)

SKU	Sales	Margin
A	10	a%
B	5	b%
C	0	-
D	0	-

Thanks in advance for your help Qlik experts!

[jwjackso]

Can the special products be associated with a special branch that all users have access to in section access?

[SBurford]

Hello Jwjackso, I think I haven't understood completely your question. This could be a valuable suggestion. Do you have an example?

[teiswamsler]

Hi SBurford

A short term solution could be to add some dummy data in the fact table.
Add ( concatenate() ) all possible SKU for each of the branch office to the fact table

Hereby all SKU will be possible to show, after Section Access have been applyed.

Br.
Teis

[SBurford · Accepted solution]

Hello Teiswamsler, 

If I understand correctly your suggestion, you'd recommend me to alter the Products table and leave a new one, as in the following example?

Branch_Offices:
Load Distinct
  [Branch Office] as Unique_Branch_Office
FROM [Sales Table];

For Each Office in FieldValueList('Unique_Branch_Office')
 New_Product_Table:
 Load Branch_Office & SKU as [Branch Office SKU];
 Load
     '$(Office)' as Branch_Office,
     *
 Resident [Product Table];

Drop [Product Table];

Also I guess I'll have to alter the original Sales Table as well:

Sales_Table_New:
Load Branch_Office & SKU as [Branch Office SKU],
     *
Resident [Sales Table];

Drop [Sales Table];

Tell me if you have any insights here or any flaw you might see here, I will try this meanwhile.