Regarding QlikSense Category Total script

[SK111]

Hello Friends, Appreciate your time! Please let me know your feedback on the scenario below.

I have a merchant fact table with merchant sales, category and stores connected to category table by category_id&store_id.

The category table has category sales grouped by category and stores. I have a requirement to get the category sales irrespective of the merchant sales for a specific merchant category.

for eg, 

merchant_table:

merchant,category_id,stores_id,sales,

merchant1,C25, S1,100

category_table:

category_id,store_id,category_sales

C25,S1,100

C25,S2,150

Since the section access is based on merchant, when a merchant logs in the category total is not getting the stores which is not a part of a specific merchant.

In this case the category sales 150 is not getting added to the category C25 total since merchant1 is not part of Store S2.

I tried to concatenate a dummy key for eg

concatenate(merchant_table)

load distinct vendor,c25&S2 as key

resident merchantfact;

But I need to find a way to concatenate only the stores they are not part of otherwise it will get duplicate

Kindly let me know your thoughts.,

Thank you!

[edwin]

from what i understand you want merchant to have access to all stores with the categories that the mechant has access to.  in this case merchant 1 should really have access to S1 and S2 stores.

the section access logic then is wrong.  your section access table should include only merchant and category

category, store and category sales should be in a separate table.

[SK111]

Hi,

Thanks for your reply. the merchant is ok. what I am looking for is to get the category share. In this case when a merchant logs in  the app reduce to category for only store S1.

I got to calculate merchant share vs category share. To get category share, I need all the stores which sells that category.

thank you!