topic Data restriction even on non-related fields in QlikView

Data restriction even on non-related fields

Mon, 28 May 2012 15:20:50 GMT

Good day,

There is a behavior I don't understand when QV is calculating an expression with a separate dimension.

Here is my model:

Data:
LOAD * INLINE [
Month, Group, Elem, Value
1, AAA, 1, 10001
1, AAA, 2, 10002
1, AAA, 3, 10004
2, AAA, 1, 10000001
2, AAA, 2, 10000002
2, AAA, 3, 10000004
3, AAA, 1, 10000000001
3, AAA, 2, 10000000002
3, AAA, 3, 10000000004
1, BBB, 3, 10008
1, BBB, 4, 10016
1, BBB, 5, 10032
2, BBB, 3, 10000008
2, BBB, 4, 10000016
2, BBB, 5, 10000032
3, BBB, 3, 10000000008
3, BBB, 4, 10000000016
3, BBB, 5, 10000000032
];
DimMonth:
LOAD DISTINCT Month AS DimMonth
RESIDENT Data
ORDER BY Month ASC;

As you can see, there is no relation between the "Data" table and the "DimMonth" table.

In a straight table, I use the following expressions:

sum({$ <Month=P(DimMonth), Group=>} Value)

to have all the values regarding the selection.

num(DimMonth)

to know the month selected.

So I can calculate an "average value" by dividing the 1st by the 2nd.

This should return the same graph if I make a selection on "Group" or not, because the first expression contains a set analysis that says "override the selection on "Month" dimension" and the second deals with dimensions that are not linked to the "Group" field.

Unfortunately, when I make a selection on "Group", I don't have all the values I want in the "num(DimMonth)" expression: it's empty in some lines, even it's there is no relation with the field where there is a selection...

I know I can use the following formula to have the correct value:

num(only({$ <Group=>} DimMonth))

but I want to understand WHY there is this behavior?

Data restriction even on non-related fields

chematos — Mon, 28 May 2012 18:04:35 GMT

I think that this behavior is because there is no relation between Month and DimMonth precisely. When you are selecting DimMonth, there is no selection about Month.

If you were using the same expression but with DimMonth instead Month, I think your formula would be right.

sum({$ <DimMonth=P(DimMonth), Group=>} Value)

I`m not sure about that but may be is the answer.

Hope this helps

Data restriction even on non-related fields

Jason_Michaelides — Mon, 28 May 2012 18:27:14 GMT

Jose is right. You need to use Concat() here:

sum({$ <Month={$(=Concat('"' & DimMonth & '"',','))}, Group=>} Value)

Hope this helps,

Jason

Data restriction even on non-related fields

Jason_Michaelides — Mon, 28 May 2012 18:28:38 GMT

PS - ignore the 'amp'. That's my iPhone misbehaving!!

Data restriction even on non-related fields

Mon, 28 May 2012 18:52:29 GMT

When you are selecting DimMonth, there is no selection about Month.

That's what I want: no direct selection, but the link is done in the set analysis.

sum({$ <DimMonth=P(DimMonth), Group=>} Value)

For what purpose ?!?

DimMonth=P(DimMonth) --> this is totally useless, isn't it ?

Jose is right. You need to use Concat() here:

No, I don't!

I don't have any problem with the "sum" expression with the set analysis, I just don't understand why the "num(DimMonth)" needs a set analysis ("num(only({$ <Group=>} DimMonth))") whereas there is no link between the selection "Month" and "DimMonth".

(please look at the screenshots)

Re: Data restriction even on non-related fields

Jason_Michaelides — Thu, 07 Jun 2012 08:28:18 GMT

Hi Nicolas,

Sorry for the delayed reply to this...if you have worked it out already then well done!

When I first looked at this and made my comment above I was on my phone on a train and couldn't open the qvw you helpfully posted. Now I have had a chance to look at it and believe I can explain this for you.

Firstly, your quote:

I know I can use the following formula to have the correct value:

num(only({$ <Group=>} DimMonth))

I don't belive this should work either (and in my tests on your app, it didn't - see attached) for the same reason as "Values/Num(DimMonth)" doesn't work.

The reason for this behaviour is that although there is no association between Group and DimMonth, you are forcing one by the use of Elem in your chart dimensions. there is an association between Elem and Group, and by using DimMonth in an expression you are forcing an association between DimMonth and Elem. Obviously, there isn't one, so when you select a Group value that changes the available Values, you get nothing. If you select certain Month values you will see the same behaviour.

Set Analysis allows you to override selections; it doesn't override chart dimension values. For this, you need to use the TOTAL qualifier. So, the correct expression for what you need is:

Values / num(only(TOTAL DimMonth))

Hope this helps,

Jason

Re: Data restriction even on non-related fields

Thu, 07 Jun 2012 09:06:45 GMT

Thanks to your explaination, I now understand a little better why there is a "link" whereas there is no link.

But why I still don't understand is why for the "sum({$ <Month=P(DimMonth), Group=>} Value)" expression, the restriction is not apllied, but on "only({$ <Month=P(DimMonth), Group=>} distinct DimMonth)" it is.

only({$ <Month=P(DimMonth), Group=>} distinct Month) --> no restriction

only({$ <Month=P(DimMonth), Group=>} distinct DimMonth) --> restriction

The rule is: there is a real relation between "Month", "Elem" and "Group", so when I modify the set analysis on 1 of those dimension (if they are not used as dimension in the chart), the set analysis is applied.

There is no relation between "DimMonth", "Elem" and "Group", so when I modify the set analysis, it does nothing for the expression that uses "DimMonth".

I don't really get the order of the tests made by QlikView.

if (link between field in expression and field in dimension)

{

1. look at the set analysis in the expression

2. make the calulation for each values

3. display all values in the dimension that have a value calculated in step "2"

} else

{

1. don't look the set analysis

2. list every values of possible for the dimension

3. then, for each of these values, make the calculation of the expression

}

sometimes it's the dimension that is firstly checked, sometimes it's the expression...

Values / num(only(TOTAL DimMonth))

Your solution with the "total" is good...

I have a tendency to forget this keyword.