Data modeling Section Access, but also applicable totals?

[DennisNorton]

I am using Qlik Sense and having difficulties determining the most efficient solution and how many applications this would take:

As a user, I want to log in to a website and view KPI's associated with only me. For security reasons, section access must be applied to the application this information pulls from and will be the only application I/other users access. However, I also at a high level on this website, want to view the same KPI's, but for my entire company, and my entire country (but no other country or company totals). This would take into account users other than myself within my company, but the Section Access I have applied has already reduced data within the application down to myself.

How would I be able to implement section access at the individual user level, but also allow them to see the total company and country numbers applicable to them? Does this require separate apps, each with different section access'?

[Anonymous]

Hi Dennis..

Can you paste your section access logic and the source data load script..

Thanks

[rupamjyotidas · Accepted solution]

This would help you i Beleive

Full Company vs Division When Section Access is... | Qlik Community

[DennisNorton]

I'll simplify the concept as it's pretty lengthy. Concept of this application is, when users enter this application, they only see their information due to SALESPERSON field in section access. If I want them to see their company totals and their country totals, it's excluding the other users amounts. Do I need a separate application(s) to mix/match these numbers properly while maintaining the section access security for individual users?

                         Sales:               Should Be:

Salesperson      $5,000               $5,000

Company          $5,000               $25,000

Country             $5,000               $175,000

Section Access;

List:

LOAD

     ACCESS,

     USERID,

     GROUP,

     SALESPERSON

FROM [lib://Excel/AccessList.xlsx]

(ooxml, embedded labels, table is SalesAccess);

Section Application;

SalesFact:

LOAD

     TRANSACTIONKEY,

     SALESPERSONKEY,

     COMPANYKEY,

     COUNTRYKEY,

     SalesAmount,

     DiscountAmount

FROM ( [lib://Excel/SalesList.xlsx]

(ooxml, embedded labels, table is Sales);

SalesPersonDimension:

LOAD

     SALESPERSONKEY,

     SALESPERSON,

     SALESFIRSTNAME,

     SALESLASTNAME

FROM SalesFile;

CompanyDimension:

LOAD
     COMPANYKEY,

     CompanyName,

     CompanyCity,

     CompanyState,

     CompanyZip

From CompanyFile;

CountryDimension:

LOAD

     COUNTRYKEY,

     CountryName,

     CountryAbbrev

From CountryFile;

[MK9885]

I'm not sure if I understood you correctly, perhaps your SA script might help.

1. User1 should see KPI related to his role only (that's understood)

2. User1 should also see all country/company KPI? (please explain more, excluding User1 all KPI data?)

You can use OMIT or Reduction in Section access to limit user to only few fields or values.

As KPI's contains measures, maybe you need to add measure to inline load

look for the example below ( and use that field name to omit)

Re: How to handle single quotes in Inline Load

Or

You can also create a custom security rule in QMC to show/hide a sheet (which can consists all KPI's) based on user name/NTNAME.

[Anonymous]

Hi Dennis..

Ideally one app should be enough .. but you have to build a little complex section access logic either by creating active

directory group with user list of having access to which company code and to which countries or add the list to your excel for testing and the section access logic could look like

Section Access;

List:

LOAD

     ACCESS,

     USERID,

     GROUP,

     SALESPERSON,

    COMPANY,

    COUNTRY

FROM [lib://Excel/AccessList.xlsx]

(ooxml, embedded labels, table is SalesAccess);

PLEASE CHECK THE ORDER COUNTRY,COMPANY,SALESPERSON OR the vice versa..

Thanks

[MK9885]

Please find the attachment.

UserId: ADMIN

Password: ADMIN

USERID     PASSWORD

ALEX	ABCD
JOHN	BCDE
SALLY	QWER

You can login as individual user and check...

You need to link each salesperson with their respective companies/countries. I did not include OMIT field in SA but you can do that too...

Loaded 3 KPI's for each user and data is visible for logged in user only for their company/country.

Same can be replicated in Qlik Sense, except you need to add USER, INTERNAL\SA_SCHEDULER,*, in Inline or the SA will not work. And the ADMIN will also come as USER and * will be his values for all the fields

[DennisNorton]

Hi Aehman,

In this example, if I login as Alex and John and Sally are both in my company and country, is there a way to see our combined sales when viewing Company and Country, but to never be able to view the sales #'s tied to John or Sally?

[MK9885]

I'm not sure if you looking for this but it was really challenging task for me who is also new to Qlik.

Find the attachment.

What I did to accommodate your requirement is created 2 fields with same values for SalesPerson but different name.

and I added OMIT for the first field name

Star is *;

SECTION ACCESS;

LOAD * INLINE [

    ACCESS, USERID, PASSWORD, COMPANY, COUNTRY, SALESPERSON, OMIT

    ADMIN, *, ADMIN, XYZ, *, ALEX

    USER, ALEX, ABCD, *, *, *, SALESPERSON

    USER, JOHN, BCDE, *, *, *, *

    USER, SALLY, QWER, *, *, *, *

    USER, , BCDE, BCD, UK, *

    ADMIN, *, ADMIN, ABC, USA, JOHN

    ADMIN, *, ADMIN, BCD, *, SALLY

];

SECTION APPLICATION;

LOAD * INLINE [

    SALESPERSON, COUNTRY, COMPANY, SALES, TOTAL, PERSON

    ALEX, USA, ABC, 10000, 35000, ALEX

    JOHN, USA, BCD, 25000, 66500, JOHN

    SALLY, UK, XYZ, 5987, 11000, SALLY

    ALEX, USA, ABC, 11278, 35000, ALEX

    ALEX, USA, ABC, 98217, 23213, ALEX

    ALEX, USA, ABC, 73767, 123213, ALEX

    JOHN, USA, BCD, 25000, 66500, JOHN

    JOHN, USA, BCD, 8798, 768886, JOHN

    JOHN, UK, BCD, 12312, 34232, JOHN

    JOHN, UK, BCD, 23987, 2354, JOHN

];

ALEX, JOHN and SALLY will see total Sales and all Companies plus all countries but individual sales and will not see individual sales from JOHN or SALLY.

So for the Individual KPI's you will need to give if condition

Each KPI are designated to show their own total sales.

Ex: In condition for ALEX KPI you'll need to give

if(osuser()='DOMAIN\ALEX',1,0)

This will show or hide the KPI with regards to NTNAME.

check the app and you'll understand.

Other way is creating hidden sheets for each user based on NTNAME again but if users are more than 100 then it will be issue hence better show/hide the kpi using NTname.

If you doing this in Qlik Sense then everything will be same but slight change in script plus you'll need a show/hide container to show or hide KPI.

Check the script it's hidden

password for hidden script is admin