topic Number to time format and distribution chart in QlikView

Number to time format and distribution chart

Thu, 14 Oct 2010 14:53:20 GMT

Hi,

I've a table with performance values from a monitoring systems.

This information is related with "response time" in seconds.

The values are between 0,001 s and 59,999 s.

How can I change it in "ms" in case value is greater than 1,000 seconds (including the units)?

0,055 -> 550ms

7,35 -> 7,35s

I need to create a graph in order to know the response time distribution. How can I create it?

(It seems easy: count how many values are in every 0,1s range and print it....)

Thank you!

Number to time format and distribution chart

pover — Thu, 14 Oct 2010 20:48:12 GMT

As for point 1, I think it would be better that the user see values that have the same units, but you should be able to do something like the following:

if(sum([Time_In_Seconds])>1000,

num(sum([Time_In_Seconds])/1000,'#.##,00 ms'),

num(sum([Time_In_Seconds]),'#.##,00 s'))

For point 2, investigate the class function in QV help or in the forum. You have to make a calculated dimension with the class function and put the count as an expression.

Regards.

Number to time format and distribution chart

Fri, 15 Oct 2010 12:00:59 GMT

1) Great!

It works!!! I use class function and it works ok when I just select one "service" in order to know his response time distribution. But I've problems selecting more than one service when "relative" is activated. (and I really need it!)

My dimensions are:

=if( performance_data.value<3, class(performance_data.value, 0.5,'segons'))

servicecheck_name

And my expression is:

Count(performance_data.value)

With this configuration:

a) selecting "service A" it has 80% of response time between 0-0,3s

b) selecting "service A and service B", service A has just 25% of response time between 0-0,3s

I suppose that Qlikview applies "relative" with all information data and not for each dimension. Isn't it? How could I solve it?

Thank ypu so much!!!

Number to time format and distribution chart

pover — Fri, 15 Oct 2010 15:54:57 GMT

Yeah, instead of using the relative option in QlikView you would have to do something like the following to always return the porcent per each service:

count(performance_data.value)
/
count(Total <servicecheck_name> performance_data.value)

I don't know know if this will work correctly with the dimension you have defined, but I hope it points you in the right direction. If you need more help upload an example of your application.

Regards.

Number to time format and distribution chart

Fri, 15 Oct 2010 17:05:06 GMT

Thank you for your answer but I'm calcuating a distribution chart, so I can't change the expression...

I need the % of occurrencies in each range.

I don't know what info could I upload, so I'll try to give you an easy example:

servicechecks Table:

ID name

1 Service A

2 Service B

performance_data Table:

ID Time value

1 d1 0,45

1 d2 0,3

1 d3 0,75

2 d4 0,75

2 d5 0,15

2 d6 1,3

So, using the previous dimension ans expression definition I was trying to get that results:

* selecting just ServiceB:

Range Ocurrencies %

0-0,5 2 66%

0,5-1 1 33%

* selecting ServiceA and ServiceB:

Range Service Ocurrencies %

0-0,5 ServiceA 2 66% (and using relative I get 33%)

0-0,5 ServiceB 1 33% (and using relative I get 16%)

0,5-1 ServiceA 1 33% (and using relative I get 16%)

0,5-1 ServiceB 1 33% (and using relative I get 16%)

1-1,5 ServiceB 1 33% (and using relative I get 16%)

So if I try to make a % manually modifying the expression, I won't calculate time in each range...

Isn't it? How could I get it?

Thank you so much.

Number to time format and distribution chart

pover — Fri, 15 Oct 2010 17:19:53 GMT

I used the formula I recommended above and it seemed to work. See the attached example.

Regards.

Number to time format and distribution chart

Fri, 15 Oct 2010 19:38:03 GMT

I'm using Qlikview Personal version, so I can't import any file....

Could you copy it as text, please?

Thank you so much!

Number to time format and distribution chart

pover — Fri, 15 Oct 2010 20:23:10 GMT

Here's the picture of the table. Using the same column names and data as above, the dimensions are:

=class(value,.5)

and

name

The expressions are:

count(distinct Time)

and

count(distinct Time)/count(distinct total <name> Time)

Regards.

Number to time format and distribution chart

Mon, 18 Oct 2010 11:46:00 GMT

You're right. It works!!!

I completely forgot the class function defining the dimension... so I couldn't understand it!

Now, I've my trellis graph and, overall, I've understand how does it work!

Thank you so much!