topic Set analysis with time diference in QlikView

Set analysis with time diference

bruno_m_santos — Thu, 15 Nov 2012 16:35:27 GMT

Hello everybody,

I've a set analysis expression where I pretend to sum the diference (HOUR_REALIZACAO-HOUR_MARCACAO), when HOUR_REALIZACAO>HOUR_MARCACAO .

In some records it works and in others no.

I don't understand what is going wrong.

Any ideas

Thanks in advance.

Bruno

Sum
({Real * Consultas* $<MonthID = {"<=$(=Max(MonthID))"},
Date = {"<=$(=Max(Date))"},
Year = {$(=Max(Year))},
HOUR_MARCACAO   -= {$(=null())},
HOUR_REALIZACAO     = {">=$(Time(HOUR_MARCACAO))"},
GROUP=
>} (HOUR_REALIZACAO-HOUR_MARCACAO))

Re: Set analysis with time diference

vgutkovsky — Thu, 15 Nov 2012 20:00:23 GMT

Bruno,

I believe your problem is HOUR_REALIZACAO = {">=$(Time(HOUR_MARCACAO))"}. An important principle of set analysis that is often misunderstood is how it treats chart dimensions. Namely, it ignores them when evaluating an expression. So, your expression Date = {"<=$(=Max(Date))"} for example, will work properly since you are checking to make sure Date is less than the overall max Date, irrespective of the current chart dimension (note that you can safely remove this expression altogether as it will always evaluate to TRUE unless Date is null). However, you can't write HOUR_REALIZACAO = {">=$(Time(HOUR_MARCACAO))"} since that tries to check the value within the current row and won't work. If you need to check this, the only way to do so would be either in the script by setting a flag, or through an IF statement in the expression.

Regards,

Vlad

Re: Set analysis with time diference

bruno_m_santos — Fri, 16 Nov 2012 10:04:41 GMT

Hi Vlad,

You are completely right. I understood my error.

Many thanks.

Bruno.