Hi,
After applying section access I realized I have a problem in the data model.
Let me shortly explain how it looks at the moment.
The phase 1 of the Qlik dashboard is to replicate an excel report - so a lot of tables, pivot tables, which in excel have quite often unrelated dimension rows.
In Qlik model I have a main big table with the information about projects. Projects have start / end / purged / on hold dates so they are connected to canonical calendar.
Also the requirement is not just to show the final values in the pivot table but also after dimensions selections be able to have a second table with all the related deals / details.
So I decided to create a custom dimensions in the script for more complicated pivot tables, i.e
CustomTable1:
ProjKey,
'My 1st row' as CustomDimension1
From a Qvd
where (conditions required for My 1st row);
concatenate(CustomTable1)
ProjKey,
'My 2nd row' as CustomDimension1
From a Qvd
where (conditions required for My 2nd row);
So I have a dimension with 2 rows which have independent where clause conditions, and are connected to the main table on ProjKey. The main table has something like a project counter (1) so in Qlik pivot table I do for example:
Dimension: CustomDimension1
Column: Canonical Month
Expression: Sum(ProjectCounter)
I am not sure whether in general this was a good approach, but I think it does what is also needed - limits projects when any of pivot dimensions is selected and I have the right projects displayed in the details table.
The problem is where I have a few tables requiring a custom row - for example My 3rd row which is division of My 2nd row and My 1st row
So the idea was to create a dummy row without any ProjectKey or where clause, so something like
concatenate(CustomTable1)
// ProjKey, commented out
'My 3rd row' as CustomDimension1
Autogenerate 1;
This doesn't have to be drillable on the front because not necessary will make a sense (maybe in sums, lets say doesn't have to).
On the front in such custom pivot table is solved it by
Dimension: CustomDimension1
Column: Canonical Month
Expression: (simplified - some additional set analysis condition to react to users selections on rows)
pick(match(only({1}CustomDimension1),
'My 1st row',
'My 2st row',
'My 3rd row'
),
Expr1,
Expr2,
Expr2/Expr1
)
That has worked, but the problem became apparent when I added section access.
Section access limits data so my dummy dimension rows without ProjKey disappeared - obviously.
I don't have any idea at the moment how to solve this problem.
Section access may limit data on 4 different fields - country, region, manager, leading manager.
How would you approach this problem.
Thanks,
Luke
