Expression Set Analysis

[#1]

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))

[Miguel_Angel_Baeyens]

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

[#3]

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

Regards

[Miguel_Angel_Baeyens]

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

[#5]

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

[Miguel_Angel_Baeyens]

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

[#7]

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.

[Miguel_Angel_Baeyens]

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

[#9]

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?