Count percentage of presence of string catagory/value

[VHolubec]

Hello,

I have a several (20) stations. Each stations reports its status every hour (string value OK or "ERROR"). - so, it´s 1:M relations - 1 stations, many status records. 

To get data to one table I made relationship table (right join).

I am trying to make an Insights summary where would be a day, station number/percentage of OK statuses in a field and then percentage/number of ERROR statuses in another. 

And then I would need to filter only those stations with number of errors higher then 20% per day.

The problems are:

1) The problem is that Calculation does not contains function for COUNT calculation of string field. So cannot add these filed to Summary table. The Summary table create a Count field, but it´s just one filed which do table in format:

Day,           Station ID,  Value,    Count

Monday,         111          OK          22

Monday          111      ERROR      2

And not table like

Day,           Station ID,   Count category OK,  Count category ERROR

Monday,         111                 22                                            2

With which further calculations of percentage per day per status could be done.

2) The stacked bar chart does not subdivide data per day - so if I have data for month (30 days) the stacked bar chart summarize all data and there is no way how to filter stations with number of errors higher then 20% per day.

Does anyone face the similar problem, or is there different attitude which would solve this? If so, any idea is welcome.

Thank you.

[LauraBecht]

Hello,
>From what I understand, you have one field for the OK and Error values, but to do the percentage calculation, you need to transform the field so you have one record for each station. One way would be to do this before bringing it into Insights, another way would be to add an OK field and an Error field to your data table in Insights and then do a summary by Day, Station ID, OK, and Error. Then I suppose you could do your percent calculations from there and make a chart based on those.
Does this help?
Regards,
Laura

[LindaBeale · Accepted solution]

If I am understanding you correctly, you can solve this using table calculations in Insights.

On your original data, add a new field for the count of error and calculate the count

IF(VALUE = “ERROR”, 1, 0)

Repeat in a second field for “OK” using IF(VALUE = “OK”, 1, 0)

Now create a summary table using Day and Station ID.  The count will be created.  Now drag over your fields for Count Error and Count OK.

You can now calculate the percentage from that summary table, dividing e.g. Count error/count.  You can create another card, e.g. a bar chart from day and percentage.  Change the Statistic on ‘Percentage’ to AVG, to ensure that it is calculating the percentage correctly depending on the number of days rather than giving the sum.

[VHolubec]

Hello @LindaBeale 

many thanks for this detailed workflow which has led to desired output! It works now like a charm.

At first I personally focused on working with status field filled with string values, which was the main mistake (so to split the one string filed to two string fields couldn´t help - anyway thank you also for tip @LauraBecht).

And at last, clarification from @LindaBeale (to use integers instead of strings in two fields) put me on the right way. So thank you very much, for help and great documented steps, it has helped me a lot!