topic Problems with Gauge Chart, Set Analysis and Networkdays in QlikView

Problems with Gauge Chart, Set Analysis and Networkdays

Mon, 11 Jul 2011 09:26:52 GMT

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?

Anybody can help me, please?

Thanks,

Rodrigo.

Problems with Gauge Chart, Set Analysis and Networkdays

kaushiknsolanki — Mon, 11 Jul 2011 09:37:24 GMT

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

Mon, 11 Jul 2011 09:43:29 GMT

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

kaushiknsolanki — Mon, 11 Jul 2011 09:48:16 GMT

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

Mon, 11 Jul 2011 09:54:16 GMT

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

kaushiknsolanki — Mon, 11 Jul 2011 10:02:05 GMT

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

Mon, 11 Jul 2011 10:19:24 GMT

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

kaushiknsolanki — Mon, 11 Jul 2011 10:24:45 GMT

HI,

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

Load

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

Mon, 11 Jul 2011 10:47:10 GMT

Hi,

Ok.

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

Thanks,

Rodrigo.

Problems with Gauge Chart, Set Analysis and Networkdays

kaushiknsolanki — Mon, 11 Jul 2011 10:50:05 GMT

Hi,

Yes you can use the variable in load statement.

Regards,

Kaushik Solanki

Problems with Gauge Chart, Set Analysis and Networkdays

Mon, 11 Jul 2011 11:15:52 GMT

Hi,

I did, but it runs the same.

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

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

SLADiff is the field created in load script.

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

kaushiknsolanki — Mon, 11 Jul 2011 11:54:43 GMT

Hi,

Can you please provide the qvw file.

Regards,

Kaushik Solanki

Problems with Gauge Chart, Set Analysis and Networkdays

erichshiino — Mon, 11 Jul 2011 11:57:14 GMT

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

Mon, 11 Jul 2011 12:17:03 GMT

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"}/> }

(networkdays(date(Opty_Creat_Dt),date(Tlcl_Answer_Dt),$(allholidays))) )

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

Mon, 11 Jul 2011 12:21:13 GMT

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

Mon, 11 Jul 2011 14:47:25 GMT

Friends!

I won this chalenge.

Thanks Kaushik and Erick.

Thank you for your support.

Best regards,

Rodrigo.