Re: Full Company vs Division When Section Access i... - Qlik Community

sweens78 · ‎2018-11-20

Dear Community

I have a requirement to show Full Company vs Division .

The problem I am facing is I have assigned Section Access on the Division field for some of users.

When a person opens Application, he only sees his Division Data, so how this is possible to show full company Data.

Is the below requirement possible when Section Access is Applied. Need Suggestions???

So for example If I have a particular expression I need to show their value and the overall value if that makes sense

=Num($(v%Assessed),'##%')

My section Access is below is working and just breaks each hospital in to it own display depending on who logs in.

How would I achieve the above?

My Section Access

Section Access;

AccessTable:

LOAD * INLINE [

ACCESS, NTNAME, HOSPITALID

ADMIN, HEALTHIRL\AA, *
USER, DSHDOM\BB, 3

   ADMIN,HEALTHIRL\CC,*
   ADMIN,ADMIN,*

USER, HEALTHIRL\DD,2
];

SecurityValues:

LOAD * INLINE [

Hospital, HOSPITALID

25, 25

20, 20

26, 26

6,6

16,16

22,22

5,5

9,9

4,4

27,27

23,23

14,14

15,15

28,28

10,10

17,17

11,11

3,3

2,2

1,1

    8,8

    7,7

    12,12

    13,13

    18,18

    19,19

    21,21

    24,24

];

concatenate(AccessTable)

load distinct HOSPITALID resident SecurityValues;

drop table SecurityValues;

Section Application;

star is *;

DataTable:

LOAD * INLINE [

HospitalID, HOSPITALID

25, 25

20, 20

26, 26

6,6

16,16

22,22

5,5

9,9

4,4

27,27

23,23

14,14

15,15

28,28

10,10

17,17

11,11

3,3

2,2

1,1

    8,8

    7,7

    12,12

    13,13

    18,18

    19,19

    21,21

    24,24

];

dplr-rn · ‎2018-11-20

Am I right in understanding that you want the section access by division but want to access certain limitef full company metrics?

sweens78 · ‎2018-11-20

Correct, They just want particular parts of the full database that show the overall mean average if that makes sense.

dplr-rn · ‎2018-11-20

I believe only way would be to create an organization level roll up table for metrics i.e. pre calculate the metrics on load and put in a table

parthesh · ‎2018-11-20

i agree that is the way we can handle this situation...

sweens78 · ‎2018-11-21

Hi Dilipranjith,

Thanks for reply could you elaborate on this?
Where would I put the code to do this? Is it within section access hidden script as above or is it in my main script where all calculations are.
I'm new the qlikview so I'm trying to make it as easy for the user.
If I want for example to see the expression below showing overall and just there division in table where would I start?
=Num($(v%Assessed),'##%')

dplr-rn · ‎2018-11-21

Not on section access.
You create a table which will not get filtered by section access e.g
Company_Metrics:
Load
'CompanyWide' as Level '//just a comment
, sum(Sales) as [Total Sales]
, sum(Cost) as [Total Cost]
resident
Fact;
so the division only you will use the existing expression and for company wide you use only([Total Sales])
even this can be made granular if needed

sweens78 · ‎2018-11-28

Hi Dilipranjilth

Thanks for the above I gotten some of it working as below,

Only problem I am having is when I use some of the formulas for companywide I'm getting errors stating 'it doesn't see the fields' yet for the 'division only' they work!

It like it doesn't see the existing fields once outside the section access.

Any ideas?

The below is working for both

Load

Num($(v%Assessed),'##.##%') as National_Assessed//

resident
Fact;

dplr-rn · ‎2018-11-28

Not entirely clear what you mean.
Can you give example of the errors and scenario a bit too

sweens78 · ‎2018-11-28

Yes Of course ,

So for example when I run the below expression on a table for just the section access individual this will work no problems

Num((count(if (Interventions_ID = '1' and TimeBSPAx_ID = '1', NEWKEYID))-Count(if (Interventions_ID = '1' and TimeBSPAx_ID = '1', PatientsNotAssessed_ID)))/(Count(NEWKEYID)-Count(PatientsNotAssessed_ID)),'##.##%')

but once I create a table for it like the this one

Load
Num((count(if (Interventions_ID = '1' and TimeBSPAx_ID = '1', NEWKEYID))-Count(if (Interventions_ID = '1' and TimeBSPAx_ID = '1', PatientsNotAssessed_ID)))/(Count(NEWKEYID)-Count(PatientsNotAssessed_ID)),'##.##%') as [National > 2rs]

resident
Fact;

I get this error

Full Company vs Division When Section Access is Applied