topic Re: aggregating maximum value with set analysis in QlikView

aggregating maximum value with set analysis

Anonymous — Tue, 20 Sep 2016 21:09:02 GMT

I have time based data that contains a few key columns as follows:

Time Stamp, device_id, status, latency, region, duration. Duration unit here is in seconds.

The status of the device could be in network or out of network.

The of status and duration indicate the status of the terminal, either in network or out of network and the duration simply shows the duration of that device either in network or out of network.

I'd like to create a chart showing total duration (in hours) of the device id when it is still considered in the network but the latency value is zero or blank. As it is reported periodically duration value keeps changing, so I'd like to select the maximum duration only for each device for each observation period. Tried set analysis like below with max value but it is not working.

sum({$<latency={"=len(trim(terminal_latency))=0"}, [status]={'*IN*'}, duration = {$(=max(duration))},[device_id]>}[duration])/3600

What is the correct expression for this? should this be better with advanced aggregation? Any suggestion?

Re: aggregating maximum value with set analysis

sunny_talwar — Tue, 20 Sep 2016 21:17:26 GMT

So you want max duration change based on different dimension? If that is true that set analysis may not work for you. or it might if you are wiling to make changes in the script?

Re: aggregating maximum value with set analysis

Anonymous — Tue, 20 Sep 2016 22:14:47 GMT

I'd like to aggregate the maximum duration for all devices for selected period of time (e.g. hour, day, etc.). My dimension in x axis is simply either one of these two

=Date(tpa_date, 'DD-MM-YYYY')

Timestamp((Ceil([Time Stamp],1/IntervalSize)),'DD-MMM-hh:mm') for dynamic interval size

Could you tell me what is the correct one?

Re: aggregating maximum value with set analysis

Anonymous — Wed, 21 Sep 2016 00:19:47 GMT

I meant what would be the correct expression for that set analysis or what would need to be done in the script?

Re: aggregating maximum value with set analysis

sunny_talwar — Wed, 21 Sep 2016 00:30:14 GMT

I think it would be easier if you can provide a sample with the expected output to try few things before I can say that this is the solution which will work for you.

Re: aggregating maximum value with set analysis

Anonymous — Wed, 21 Sep 2016 01:28:28 GMT

Please find attached the sample file.

BTW, Since date and time is in separate column I made another filed namely Time Stamp in the script

Timestamp(Floor(Timestamp(Date&' '&Time,'DD/MM/YYYY hh:mm:ss'),1/288)) as [Time Stamp],

And also extracted the duration from the status and converted it seconds (e.g. IN_NETWORK 1 h 57 min 50 sec becomes 1*3600+57*60+50 sec)

I can't provide the expected output as this is what I'd like to see.

I simply want to select the maximum value for the duration when the status is in network and latency is blank or null sum these max values for each customer_id in the desired period (e.g. one day).

Re: aggregating maximum value with set analysis

Anonymous — Wed, 21 Sep 2016 01:31:20 GMT

Here is the attachment

Re: aggregating maximum value with set analysis

Digvijay_Singh — Wed, 21 Sep 2016 05:40:01 GMT

Check if attached sample can help.

Re: aggregating maximum value with set analysis

Digvijay_Singh — Wed, 21 Sep 2016 05:46:51 GMT

It calculates duration in seconds in script for IN_Network status, I am confused which dimension you want to keep in chart out of customer_id, Date, network Id( I think you mentioned it a device id?). But I am sure based on this sample you can ask specific questions now.

Re: aggregating maximum value with set analysis

Digvijay_Singh — Wed, 21 Sep 2016 07:04:36 GMT

Included timestamp dimension and line chart, corrected bug in duration calculation -

Re: aggregating maximum value with set analysis

Anonymous — Wed, 21 Sep 2016 07:44:09 GMT

Hi Fikri,

Try this:

Sum(Aggr(

sum({$<latency={"=len(trim(terminal_latency))=0"}, [status]={'*IN*'}, duration = {$(=max(duration))},[device_id]>}[duration]),[device_id]))/3600

You will calculate max duration for each device_id

Regards!

Re: aggregating maximum value with set analysis

Anonymous — Wed, 21 Sep 2016 12:22:57 GMT

Hi, I am still using personal version and unable to open the file. I wonder if it is possible to show the script and expressions used in the chart? and also a capture of the chart itself so that I can understand if it is what I am looking for.

Re: aggregating maximum value with set analysis

Anonymous — Wed, 21 Sep 2016 12:24:38 GMT

This expression is not showing any result although it shows OK.

Re: aggregating maximum value with set analysis

Digvijay_Singh — Wed, 21 Sep 2016 12:30:18 GMT

Here is the script -

Source:

Load Date,

Time,

Timestamp(floor(Timestamp#(Date&' '&Time,'DD-MM-YYYY hh:mm:ss'),1/24),'DD-MM-YYYY hh:mm:ss') as [Time Stamp],

[network id],

[area number],

customer_id,

type,

Status,

latency,

region,

rangesum(subfield(Duration,':',-1),subfield(Duration,':',-2)*60,subfield(Duration,':',-3)*3600,subfield(Duration,':',-4)*86400)/3600 as Duration

;

LOAD Date(Date#(Date,'DD/MM/YYYY'),'DD-MM-YYYY') as Date,

Time,

[network id],

[area number],

customer_id,

type,

Status,

if(wildMatch(trim(Status),'*IN_NETWORK*'),replace(KeepChar(Status,'0123456789 '),' ',':'),0)

as Duration,

latency,

region

FROM

[Data_Sample for qlik2..csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Re: aggregating maximum value with set analysis

Digvijay_Singh — Wed, 21 Sep 2016 13:05:36 GMT

Dimension -

Timestamp and customer_id

Expression

Max(Aggr(Max({$<latency={"=len(trim(latency))=0"},Duration = {">0"}>}Duration),[network id]))

I think expression needs improvement

Re: aggregating maximum value with set analysis

Anonymous — Wed, 21 Sep 2016 13:05:55 GMT

Try this, i used Interval to show it into seconds.

Interval(Sum(Aggr(max({$<latency={0},[Status]={'*IN*'},[network id]>} Time),[network id])),'ss')

Regards!

Re: aggregating maximum value with set analysis

Anonymous — Wed, 21 Sep 2016 13:53:01 GMT

Thanks Digvijay,

Actually I just want to have a simple one. So, in this case the dimension is just simply time, not necessary with customer_id. What I want to see in the chart is the Total sum of maximum duration for each customer when the latency is null or blank. In other words, I just want to select the maximum value for each customer for that duration. If the selected time interval is hour, then select the maximum value for that customer within that hour and aggregate/sum with the max value for all other customers within that period. If the time selection is one, this will become the max value for one day.

The x axis or dimension can be hour or day depending on the selection. I do this by selecting the predefined interval in the script.

Does this expression

Max(Aggr(Max({$<latency={"=len(trim(latency))=0"},Duration = {">0"}>}Duration),[network id])) do this job?

I was thinking something like:

Aggr(Max({$<latency={"=len(trim(latency))=0"},Duration = {">0"}, [status]={'*IN*'},>}Duration),[customer_Id] but not sure if this gives the result I wanted since some days show zero results where I know there are some values on those days.

Any suggestion?

Re: aggregating maximum value with set analysis

Anonymous — Wed, 21 Sep 2016 14:42:01 GMT

Or this one - if I wanted to aggregate it by date

Max(Aggr(Max({$<latency={"=len(trim(latency))=0"},[Duration] = {">0"},[Status]={'*IN*'}>}[Duration]),Date,[Customer_Id]))/3600

The unit will in hour

Re: aggregating maximum value with set analysis

Anonymous — Wed, 21 Sep 2016 14:53:30 GMT

Have you seen my response?

Use this expression:

Interval(Sum(Aggr(max({$<latency={0},[Status]={'*IN*'},[network id]>} Time),[network id])),'ss')

If you want to see it in hours change ss with hh

Regards!

Re: aggregating maximum value with set analysis

Anonymous — Wed, 21 Sep 2016 15:58:57 GMT

HI,

I did try this one with sample file and script from Digvijay. This is not showing any data regardless of ss or hh.

What is the dimension in this case?

Interval(Sum(Aggr(max({$<latency={0},[Status]={'*IN*'},[network id]>} Time),[network id])),'ss')

The above seems aggregating by network_id

I'd like to sum max value of duration with selected condition (latency is blank and customer in network) by customer_id over period of time)