15 Replies Latest reply: Jul 11, 2011 10:47 AM by Rodrigo Almeida

# Problems with Gauge Chart, Set Analysis and Networkdays

Hi Guys,

I'm problem with a formula into a gauge chart, here is:

=avg ( {\$< EstadoReport = {"Em Curso","Em Contacto","Fecho Sucesso","Fecho Insucesso"},
Opty_Creat_Dt = {">=\$(#=vInicio)<=\$(#=vFim)"} > } (networkdays(date(Opty_Creat_Dt),date(Tlcl_Answer_Dt),\$(allholidays))) )

I want to know the average in the last 90 days but using the networkdays.

"allholidays" is a variable with all holidays in my country.

This formula give "no data value" but I don't know why.

If I use:

=avg ( {\$< EstadoReport = {"Em Curso","Em Contacto","Fecho Sucesso","Fecho Insucesso"},
Opty_Creat_Dt = {">=\$(#=vInicio)<=\$(#=vFim)"} > } ALL (networkdays(date(Opty_Creat_Dt),date(Tlcl_Answer_Dt),\$(allholidays))) )

...works fine.

But I cannot use ALL.......

What I can to do?

Thanks,

Rodrigo.

• ###### Problems with Gauge Chart, Set Analysis and Networkdays

Hi,

There is one mistake i found in this expression. i.e the use of variable.

Try this expression - not sure that this will give you desired output, but variable's should be used like this.

=avg ( {\$< EstadoReport = {"Em Curso","Em Contacto","Fecho Sucesso","Fecho Insucesso"},
Opty_Creat_Dt = {">=\$(#vInicio)<=\$(#vFim)"} > } (networkdays(date(Opty_Creat_Dt),date(Tlcl_Answer_Dt),\$(allholidays))) )

Regards,

Kaushik Solanki

• ###### Problems with Gauge Chart, Set Analysis and Networkdays

Kaushik,

I'm sorry... but it occours the same.

It's no value data with both formulas.

Thanks,

Rodrigo.

• ###### Problems with Gauge Chart, Set Analysis and Networkdays

Hi,

Can you tell me what actually you want to do and for what purpose.

Regards,

Kaushik Solanki

• ###### Problems with Gauge Chart, Set Analysis and Networkdays

Hi,

I want to know the average number of days (working days) that enlapses between the dates (updated date and creation date).

Thanks,

Rodrigo.

• ###### Problems with Gauge Chart, Set Analysis and Networkdays

Hi,

How can it be the average, it should be the count of the days right...?

If not can you explain with an example.

Regards,

Kaushik Solanki

• ###### Problems with Gauge Chart, Set Analysis and Networkdays

Hi,

I want to know the average... it is not a count.

Exemple:

Id          Creation Date       Update Date       *Diff (upd date - creat date)

aaa       2010-12-22           2010-12-24          2

bbb       2010-12-21           2010-12-26          5

ccc       2010-12-23           2010-12-26          2  (christmas holiday)

ddd       2010-12-21           2010-12-22          1

Total count of the days: 10

Average: 2,5 days

In the other words, the date is closed on average 2 and a half days.

Thanks,

Rodrigo.

• ###### Problems with Gauge Chart, Set Analysis and Networkdays

HI,

If you want to do same thing as you said then, you can calculate the day difference in load time only like this,

ID,

Creation_Date,

Update_Date,

Update_Date - Creation_Date as Day_Difference

From

Xyz.

Then  use the field Day_Difference to calculate the Average.

Regards,

Kaushik Solanki

• ###### Problems with Gauge Chart, Set Analysis and Networkdays

Hi,

Ok.

Can I use in the load script the variable \$holidays??

Thanks,

Rodrigo.

• ###### Problems with Gauge Chart, Set Analysis and Networkdays

Hi,

Yes you can use the variable in load statement.

Regards,

Kaushik Solanki

• ###### Problems with Gauge Chart, Set Analysis and Networkdays

Hi,

I did, but it runs the same.

= avg ( {\$< EstadoReport = {"Em Curso","Em Contacto","Fecho Sucesso","Fecho Insucesso"},

Opty_Creat_Dt = {">=\$(#vInicio)<=\$(#vFim)"} > } (SLADiff) )

But... there is no data value.

If I use:

= avg ( {\$< EstadoReport = {"Em Curso","Em Contacto","Fecho Sucesso","Fecho Insucesso"},

Opty_Creat_Dt = {">=\$(#vInicio)<=\$(#vFim)"} > } ALL (SLADiff) )

works fine, but I cannot use ALL in that expression...

I really dont know what to do.

Thanks,

Rodrigo.

• ###### Problems with Gauge Chart, Set Analysis and Networkdays

Hi,

Can you please provide the qvw file.

Regards,

Kaushik Solanki

• ###### Problems with Gauge Chart, Set Analysis and Networkdays

Hi,

I cannot send the file now because there is a lot of customer's data.

But..... I 'd tried a test...

If I use the expression without the agregation of dates in set analysis, it works fine:

= avg ( {\$< EstadoReport = {"Em Curso","Em Contacto","Fecho Sucesso","Fecho Insucesso"}/> }

I think the problem is in the agregation

Opty_Creat_Dt = {">=\$(#vInicio)<=\$(#vFim)"}

However I need to used that to agregate the status (EstadoReport field) and agregate the last 90 days.

What can I do??

Thanks,

Rodrigo.

• ###### Problems with Gauge Chart, Set Analysis and Networkdays

Hi, Rodrigo.

Do you know why it works with All? This would ignore selections and dimensions in a chart?

Are you using any dimension on the gauge?

Does it work with the 1 instead of \$?

avg ( {1< EstadoReport = {"Em Curso","Em Contacto","Fecho Sucesso","Fecho Insucesso"},

Opty_Creat_Dt = {">=\$(#vInicio)<=\$(#vFim)"} > } (SLADiff) )

(... a lot of questions to try to better understand your problem)

Regards,

Erich

• ###### Problems with Gauge Chart, Set Analysis and Networkdays

Hi Erich,

I'm not using dimensions.

I'm just using that expression.

I need to know the average count of days (it is a diference date between Update Date and Creation Date), agregate by Status and las 90 days.

But I really dont know why does not work without ALL function....

Do you have an idea?

Thanks,

Rodrigo.

• ###### Problems with Gauge Chart, Set Analysis and Networkdays

Friends!

I won this chalenge.

Thanks Kaushik and Erick.