topic Re: Build Logic to repetitive devices as 1, using loops/sum/count/if/set analysis in QlikView

Build Logic to repetitive devices as 1, using loops/sum/count/if/set analysis

himani_a_agrawa — Wed, 16 Jan 2019 16:39:05 GMT

Hi All,

Please help me building logic on below scenario.

I have a data(attached), in which there are many device saying A/B/C.., with their server up/down status being 1/0 respectively and dates(24 Hour) correspondingly to it.

What I want here is to Count No. of distinct devices in a dataset, which are UP in entire day for atleast once. Means, If any device is Up i.e 1 for atleast once in a day, then it is counted as 1, and check for other devices and count the others similarly. and finally show the total UP devices Reported. Vice Versa for the devices which were Down whole day.

I am sorry, if I am putting this again, But I did'nt find any post regarding this.

I am not sure which function/loop will give the correct logic? Can we do it through loop, or set analysis can do this?

I am attaching a sample qvw with sample data-set, if anybody can help in building this logic, will be appreciable.

Thanks

Re: Build Logic to repetitive devices as 1, using loops/sum/count/if/set analysis

m_woolf — Wed, 16 Jan 2019 17:27:08 GMT

Is this close to what you want?

Re: Build Logic to repetitive devices as 1, using loops/sum/count/if/set analysis

vvira1316 — Wed, 16 Jan 2019 19:56:26 GMT

Hi,

Please see attached. Not sure if that is what you are looking for. if not please advise expected results.

I've added few records for verification.

Re: Build Logic to repetitive devices as 1, using loops/sum/count/if/set analysis

himani_a_agrawa — Thu, 17 Jan 2019 07:25:16 GMT

Any other way? Since KPIs cannot be calculated, table is not our requirement. I want to make two KPI which will show Todays(Max date) Up/Down devices. and if we change dates from list box, then the KPIs also gets changed.

But Keeping the logic in place, that if device isis Up i.e 1 for more than once/six times in a day(24 hour time) then the device should be counted as 1. or if it is down whole day then counted in total Down devices.

Attached snip, is just an example to show, since my No are not coming correctly, hence Ups and downs sum are not equal to the total.

Re: Build Logic to repetitive devices as 1, using loops/sum/count/if/set analysis

himani_a_agrawa — Thu, 17 Jan 2019 07:26:59 GMT

Not Sure, Since KPIs are not limited by date field.

I want UP or down changes as I select date.

Re: Build Logic to repetitive devices as 1, using loops/sum/count/if/set analysis

vvira1316 — Thu, 17 Jan 2019 13:01:41 GMT

Hi,

KPI would change as per date selection or device selection. We can restrict certain behavior or achieve certain behavior. I've done count based on timestamp. I'm not able to completely understand your requirement.

Please see attached and advise changes as per your requirement. What happens if device is up less than 6 times in a day? Is it counted in down device?

BR,

Vijay

Re: Build Logic to repetitive devices as 1, using loops/sum/count/if/set analysis

himani_a_agrawa — Wed, 23 Jan 2019 04:11:04 GMT

Thanks. The logic of making two table for UP & Down devices worked fine and satisfied my requirement. But What if we wanted to see another KPI, in which we need the Count of Devices which is Down for 3 days/7 days. Do we need to make another two tables. (Means For 1 device, Up field remains 0 for last 3 days, and hence show the count)

Please Suggest.

Re: Build Logic to repetitive devices as 1, using loops/sum/count/if/set analysis

vvira1316 — Wed, 23 Jan 2019 13:13:51 GMT

Calendar functionality may help. Can you please provide a sample data set with expected result? It will help to understand your need properly.

Re: Build Logic to repetitive devices as 1, using loops/sum/count/if/set analysis

himani_a_agrawa — Thu, 24 Jan 2019 06:16:19 GMT

Sure.

Attached is the file.

Re: Build Logic to repetitive devices as 1, using loops/sum/count/if/set analysis

vvira1316 — Thu, 24 Jan 2019 18:44:37 GMT

Hi,

I've created a data model that will provide information if device was up or down in rolling 3/7 days. Please verify if information in the table looks right. If it looks right then we can work on coming up with expressionx for KPIs or charts.

Some of the table and/or fields may be dropped from the data model.

BR,

Vijay

Re: Build Logic to repetitive devices as 1, using loops/sum/count/if/set analysis

himani_a_agrawa — Thu, 24 Jan 2019 19:53:52 GMT

Can you please explain me your logic, I am unable t collect facts.

Also, How can we use expression, to exactly count no of devices down for 3/7 days.

Re: Build Logic to repetitive devices as 1, using loops/sum/count/if/set analysis

vvira1316 — Fri, 25 Jan 2019 01:11:20 GMT

Hi,

There was an error in calculation that I've corrected. I've included new QVW file as well as an Excel file that will help you understand the data scenario. You can compare information between Excel and QVW.

I hope explanation in word document will aid in understanding.

Data Model can be cleaned up and some additional/meaningful flag can be created for calculations.

KPIs and Expressions will have to be worked out using Set Analysis.

Let me know if you will need any further help.

BR,

Vijay

Re: Build Logic to repetitive devices as 1, using loops/sum/count/if/set analysis

himani_a_agrawa — Sat, 26 Jan 2019 06:20:59 GMT

Can we simplify the data model, since I already have other data table, which is making it more complex.

Attached is the snip of showing my data tables.

Re: Build Logic to repetitive devices as 1, using loops/sum/count/if/set analysis

vvira1316 — Sat, 26 Jan 2019 13:36:16 GMT

Here is the simplified file