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!