topic Re: Set Analysis for Rolling Days to know Count of Up/Down Devices in QlikView

Set Analysis for Rolling Days to know Count of Up/Down Devices

himani_a_agrawa — Sat, 16 Nov 2024 02:03:45 GMT

Hi All,

Please help me building logic on below scenario.

I have a data, in which there are many device saying A/B/C.., with their hourly server up/down status being 1/0 - up/down respectively.

What I want here is the Count of Devices which were completely down for 1 day/ 3 day/ 7 Day.

The down devices should be the one, which are down for the whole day, i.e for a day, there will be 24 sample of one device, that should be 0 (down) then only that device is considered as down.

And in same way, those devices which were up(i.e 1) at least once in a day. then it is counted as Up device,

I want to do this through set analysis.

For achieving this, I am using below expression, but not getting 100% result.

=Count(distinct If(aggr(sum({<DaysAgo={">=0<=3"},DeviceType={'controllers'},DeviceName-={''}>} is_up),DeviceName,Date)=0, [DeviceName]))

Is_up - Up/down status (1/0)

Please Note:

1 day - Device down for whole 1 Day (31st oct i.e 24 hour)

3 day - Device down for all the 3 Dates (consecutive dates, as 31st oct, 30th oct, 29th oct)

7 day - Device down for all the 3 Dates (consecutive dates, as 31st oct, 30, 29, 28,27,26,25 oct)

Also, the 7 day down devices should not be in the 3 day down devices, and same in 1 day down devices. they should be unique to each other)

Please help me on this , Not sure, if this expression needs correction?

Thanks in Advance!

Re: Set Analysis for Rolling Days to know Count of Up/Down Devices

sunny_talwar — Fri, 01 Nov 2019 11:39:29 GMT

The sample from the other post doesn't have is_up field in it... would you be able to share another sample where we can check this out?

Re: Set Analysis for Rolling Days to know Count of Up/Down Devices

himani_a_agrawa — Tue, 05 Nov 2019 04:18:35 GMT

Here is the sample file.

Re: Set Analysis for Rolling Days to know Count of Up/Down Devices

sunny_talwar — Tue, 05 Nov 2019 11:39:59 GMT

I will take a look at it and get back to you if in case I have questions.

Re: Set Analysis for Rolling Days to know Count of Up/Down Devices

sunny_talwar — Tue, 05 Nov 2019 12:35:38 GMT

Check attached

Re: Set Analysis for Rolling Days to know Count of Up/Down Devices

Brett_Bleess — Thu, 07 Nov 2019 19:56:48 GMT

Himani, did Sunny's sample app get you what you needed? If so, do not forgot to return to the thread and use the Accept as Solution button on that post of Sunny's to give him credit for the help and let others know the solution worked. If you are still working on things, leave an update.

Regards,
Brett