topic Re: Set analysis with initial data reduction in QlikView

Set analysis with initial data reduction

Wed, 04 Dec 2013 10:30:29 GMT

Hello,

I am trying to apply a set of charts with set analysis data. My intent is to show some gauges with different perspectives of sales.

My problem begins when by the access control, the initial data reduction reduces data for the other perspectives and all gauges show the same information.

Does anyone knows how to solve this?

Thanks in advance

Re: Set analysis with initial data reduction

Yousef_Amarneh — Wed, 04 Dec 2013 11:26:33 GMT

could you please upload an example

Re: Set analysis with initial data reduction

Wed, 04 Dec 2013 11:59:08 GMT

Hi Yousef

Thanks for response!

So.. I had an access section at my document limiting data to users by initial reduction like this:

Section Access;

LOAD * INLINE [

ACCESS, NTNAME, PASSWORD, GROUP, GROUP1, GROUP2

ADMIN, foo\bar, *, ADM, ADM, ADM

];

LOAD DISTINCT

'ADMIN' as ACCESS,

'foo\' & upper(trim(USU_LOGIN)) as NTNAME,

'*' as PASSWORD,

If(upper(trim(USU_KIND)) = 'SALESMAN', upper(trim(USU_LOGIN)), 'ADM') as GROUP,

If(upper(trim(USU_KIND)) = 'INSPECTOR', upper(trim(USU_LOGIN)), 'ADM') as GROUP1,

If(upper(trim(USU_KIND)) = 'MANAGER', upper(trim(USU_LOGIN)), 'ADM') as GROUP2

FROM [..\TRA\REF\Users.QVD] (qvd);

Section Application;

STAR IS * ;

SALESMAN_ACCESS:

LOAD upper(trim(USU_LOGIN)) as GROUP,

USU_SALESMANCOD as SALESMAN_CODE

FROM [..\TRA\REF\Users.QVD] (qvd)

Where upper(trim(USU_KIND)) = 'SALESMAN';

LOAD * INLINE [

GROUP, SALESMAN_CODE

ADM, *

];

INSPECTOR_ACCESS:

LOAD upper(trim(USU_LOGIN)) as GROUP,

USU_INSPECTORCOD as INSPECTOR_CODE

FROM [..\TRA\REF\Users.QVD] (qvd)

Where upper(trim(USU_KIND)) = 'INSPECTOR';

LOAD * INLINE [

GROUP1, INSPECTOR_CODE

ADM, *

];

MANAGER_ACCESS:

LOAD upper(trim(USU_LOGIN)) as GROUP,

USU_MANAGERCOD as MANAGER_CODE

FROM [..\TRA\REF\Users.QVD] (qvd)

Where upper(trim(USU_KIND)) = 'MANAGER';

LOAD * INLINE [

GROUP2, MANAGER_CODE

ADM, *

];

This section with initial data reduction limits and works properly in what data the respective users can see.

At few weeks, we have the necessity to set some gauges with analysis. So I created some variables with set analysis expressions

For the sales vs planning for entire business I ignore all managers, inspectors and salesman dimensions.

BUSINESS_SALES = (sum({$<MANAGER_CODE=,INSPECTOR_CODE=,SALESMAN_CODE=>} SALES_AMOUNT) / sum({$<MANAGER_CODE=,INSPECTOR_CODE=,SALESMAN_CODE=>} SALES_PLANNED)

For the sales vs planning for managers I set to ignore Inspector and salesman dimensions, and set all possibilities to the manager selected dimension;

MANAGER_SALES =

(sum({$<INSPECTOR_CODE=,SALESMAN_CODE=,MANAGER_CODE=P(MANAGER_CODE)>} SALES_AMOUNT) /

sum({$<INSPECTOR_CODE=,SALESMAN_CODE=,MANAGER_CODE=P(MANAGER_CODE)>} SALES_PLANNED)

For the sales vs planning for inspectors, I set to ignore Manager and Salesman dimensions, ant set all possibilities to the inspector selected dimension.

INSPECTOR_SALES =

(sum({$<INSPECTOR_CODE=P(INSPECTOR_CODE),SALESMAN_CODE=,MANAGER_CODE=>} SALES_AMOUNT) /

sum({$<INSPECTOR_CODE=P(INSPECTOR_CODE),SALESMAN_CODE=,MANAGER_CODE=>} SALES_PLANNED)

Finally for the salesman sales vs planning, I set to ignore both manager and inspector dimensions and set all possibilities to the salesman selected dimension.

SALESMAN_SALES =

(sum({$<INSPECTOR_CODE=,SALESMAN_CODE=P(SALESMAN_CODE),MANAGER_CODE=>} SALES_AMOUNT) /

sum({$<INSPECTOR_CODE=,SALESMAN_CODE=P(SALESMAN_CODE),MANAGER_CODE=>}

SALES_PLANNED)

Those variables when set to Gauge charts and executed in manager access shows exactly what they have to show. The problem occurs when a salesman, for example, access the document. The initial data reduction limits data and all gauges shows the salesman result.

Do you have some idea how can I solve this?

Thanks again

Re: Set analysis with initial data reduction

Yousef_Amarneh — Wed, 04 Dec 2013 12:06:54 GMT

are you sure that you checked these check boxes ? this is the document settings window

Re: Set analysis with initial data reduction

Wed, 04 Dec 2013 12:08:07 GMT

Yeap, both are checked

Re: Set analysis with initial data reduction

Wed, 04 Dec 2013 12:46:47 GMT

Oh my, Oh my, Oh My!!!!

After a lack of comprehension, the answer jumps above my screen!!!

Ignoring the GROUP access dimension too at all variables my problem it´s finally solved!!!

🙂

Thank you so much Yousef!

Re: Set analysis with initial data reduction

Fri, 06 Dec 2013 10:14:33 GMT

No, the previous thread do no works properly. 😞

Bu the good news are that I found the solution! 🙂

The first thing that I did was tell to Qlikview do not reduce my data (red) and do the initial selection by section access (green)

After to do that I set "On open Triggers" at document properties, triggers tab.

So I set all my section fields to blocked at open document

With this, the users cannot change their pre selected access dimensions.

Finally I hide the section dimension fields like above:

Setting the HidePreffix like any char that you like, and then setting the section permissions fields to the previous preffix this fields will be hide at your selections;

Voilà!

The same behavior, with no data reduction and now My Set analysis expressions can be done! 🙂