topic Calculated dimension / set analysis expression in QlikView

Calculated dimension / set analysis expression

Wed, 06 Mar 2013 14:17:24 GMT

Hello,

I have attached an example application.

I don't understand something here.

I have a calculated dimension with a "IF" condition and the "suppress null' option checked.

First, I want only display the values of my dimension which validate the condition on the dimension formula.

It's seems not work here as when I select a Date which is not the one in condition, the dimension still display values outside the selection range.

So the "supress null" option don't work for some reason (my set analysis expression coumns may be the cause?)

Second, I want count all the ID on 2013 by Group without taking account the dimension condition or the selections,

while the group in the table must be only the one which validate the selection and dimension formula.

The {1} clause seems not do the trick here. Add aggr function call do it but it's not what I want and the dimension stil display out of selection scope values

I guess the dimension still taken in account in spite of the {1} ? Any method to override it on my expression column without use aggr?

I have some columns to try to do this, but I want if possible get this result with set analysis without aggr function as the real application is really more complex and on huge volume (> 20 millions line on 20 columns) it's better for performances.

I can't edit load script, so I want this only using expressions.

Could you help me?

Thanks.

Re: Calculated dimension / set analysis expression

Gysbert_Wassenaar — Wed, 06 Mar 2013 14:36:27 GMT

Use Group as dimension and as expression: count({1<Year={'2013'},Group=p({<Date={'2013-01-01'}>}Group)>} DISTINCT Items)

Re: Calculated dimension / set analysis expression

Wed, 06 Mar 2013 14:57:15 GMT

Hello,

Thanks, but I can't do this as in the real application the dimension is really more complex :

dimension is a top 10 calculated on user selection with rank condition on sub aggregate, and with 10 or more different expression columns.

All expressions must be aggregated by the dimension taking account of the "IF" clauses (or not on some case like on my example).

All expression must be evaluated,grouped on the dimension value printed (so in example the Group value).

For simple count with some other filter set analysis, on current selection only, it seems ok :

dimension value + "IF" condition hidden in the dimension formula + filter set analysis on current selection, seems all used in the formula.

But for more complex ones where I want NOT take the "IF" clause of the dimension and/or limit to the current selection,

I can't find other way than add an AGGR call group by the Group field.

I try also some things with TOTAL clause but It changed nothing and I don't think It's useful here.

Re: Calculated dimension / set analysis expression

johanlindell — Wed, 06 Mar 2013 17:09:05 GMT

Here's a solution:

Create a field 20130101 with a "flag" and a 2013-field with a "flag".

In my example I've made a left join, but you should do it while loading the base table.

Re: Calculated dimension / set analysis expression

Mon, 18 Mar 2013 11:01:49 GMT

Hello,

Thanks for the help.

Unfortunatly I can't do like this as the Date condition use in fact a variable.

My error to not correctly write it in my example application .

The dimenson condition is in fact Date=vDate with vDate a variable updated by the users.

There is two date:

1-the one fixed in the variable vDate used in dimension condition to resraint to a specific date

2-the one selected by the field Date to have the status of my data at the selected date

The (2) is used to have the data status at a specific time considering only the ID of the data which were available the (1)

As it must remain dynamic it's not possible to use a flag here as I have thousands date possible.

Re: Calculated dimension / set analysis expression

johanlindell — Mon, 18 Mar 2013 13:54:00 GMT

That makes it tricker.... I think you should look into solving this in another way. Why not add a "DayInYear"-field and use this in your expression to find the values on the first day of the year. And change the Group-dimension to be the "real" group and not a calculated expression.

See attached.

/Johan