Set analysis with time diference

[bruno_m_santos]

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

[vgutkovsky · Accepted solution]

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

[bruno_m_santos]

Hi Vlad,

You are completely right. I understood my error.

Many thanks.

Bruno.