topic Expression Set Analysis in QlikView

Expression Set Analysis

Wed, 07 Dec 2011 12:40:26 GMT

I want put this expression in qlikview with Set Analysis, anyone know how can i do this?

This expression takes long time to run .. I need Help!

=if(count(distinct WT_WorkDateStart=(date(vWsCalendarMon )&' '&text(left(time(TimeFinal),2)) & ':' & date(TimeFinal_1,'mm') & ':' & date(TimeFinal_1,'ss')))=2,1, if(count(distinct WT_WorkDateEnd=(date(vWsCalendarMon )&' '&text(left(time(TimeFinal),2)) & ':' & date(TimeFinal_1,'mm') & ':' & date(TimeFinal_1,'ss')))=2,2,0))

Expression Set Analysis

Miguel_Angel_Baeyens — Wed, 07 Dec 2011 13:01:54 GMT

Hi,

It seems you are getting a timestamp (day, month, year, hours, minutes and seconds) and you are doing a lot of string working with that. QlikView has some date and timestamp functions that can help you doing that part. Can you please share an example of a record for WT_WorkDateStart, TimeFinal and TimeFinal_1 so we can see its format?

On the other hand, the possible values of that expression, if I'm not wrong are 0, 1 and 2. Set analysis will not return that, and you will need a conditional If() anyway, but it may help you with the string functions. In call cases, passing that calculation to the script may be a good idea and worth trying, since it will improve your chart performance notably.

Regards.

Miguel

Expression Set Analysis

Wed, 07 Dec 2011 13:12:03 GMT

Hi Miguel, thanqs for your answer, but i thought in Set Analysis it was very faster, am I wrong?

Regards

Expression Set Analysis

Miguel_Angel_Baeyens — Wed, 07 Dec 2011 14:40:45 GMT

Hi,

You're right: set analysis is faster than any conditional. Actually the larger your data set is, the faster set analysis will perform compared to an If(), to the extent that in some cases an If() will cause an error, and set analysis will eventually render the chart.

But set analysis cannot replace a conditional in some cases, and you may need an If() for that chart. As I mentioned above, set analysis is evaluated once and for the whole chart, and then the expressions are evaluated based on that set of selections (set analysis is no more and no less than a set of field selections).

In other words, set analysis cannot return a conditional value (1 here, 2 there, 3 everywhere...) that seems to be your case. That's why I asked about your data and your requirements, because if your dataset is huge enough, you may need to pass some aggregations to the script so you needn't a conditional.

Hope that makes some sense.

Miguel

Expression Set Analysis

Wed, 07 Dec 2011 15:30:05 GMT

Miguel, i have one question:

I have one pivot table with hours from 00:00 h to 23:00 h (first dimension) and in the second dimension i have minutes on the hour and I wanted a condition to have values, then the pivot table expanding, else collapse the table or the columns. Do you understand? Regards

Expression Set Analysis

Miguel_Angel_Baeyens — Wed, 07 Dec 2011 15:39:25 GMT

Hi,

In the same way you build a master calendar, based usually on a date (day, month, year) and you create as many date dimensions as you need (quarter, month, monh year, week, day of week, season... you name it) you should create one calendar based on hours with more time related dimensions (minutes, seconds and so on). This will make easier to display which times correspond to which dates.

Hope that helps.

Miguel

Expression Set Analysis

Wed, 07 Dec 2011 15:47:01 GMT

No, i want say this: if (Count(fieldx)=1, expand,collapse)

expand=expand column in pivot table

collapse=collapse column in pivot table

Do you know any way for make this condition,please?

Regards.

Expression Set Analysis

Miguel_Angel_Baeyens — Wed, 07 Dec 2011 15:56:24 GMT

Hi,

That's the way I'd do it in one chart. But rather I'd create two different charts, one with each expression, and show or hide the chart depending on a variable value, for example. That means more work (every changes has to be done twice) but far better performance. If that's not what you want, I'm afraid I'm not getting you.

Regards,

Miguel

Expression Set Analysis

Wed, 07 Dec 2011 16:05:43 GMT

I only have a pivot table. no graph.
And I want to expand a single dimension if my condition is true, but do not know how.
I was more clear?