Finding the count of days in the current month where a goal is reached

[#1]

Hi Community,

In our data, I have the number of tests we run per day, and the number of each of these that fail.  Our goal is, say, 99.5%

So successrate is 1-( sum(errors) / sum(tests) )

I need to sum tests and errors by date, then count the number of days this month that the successrate is >= 99.5%

Do I need to group the stats in the load script by day?  If so, how?  Also, We have only test timestamp, which I have rounded to daystart in the load statement. 

Hope that makes sense!

Thanks in advance for your help,

[chematos]

HI,

I would calculate 1-(sum(errors) / sum(tests)) in the load statement, grouping by day, for example calling it Goal

I don´t know how do you want to create the table to visualize data,but I wolud create a pivot table with two dimensions: month and date

If you select partial sums for date dimension, you can do something like this in one expression:

if( Dimensionallity() = max(Dimensionallity() ), Goal,

count({<Goal={'>=0.995'}total Goal))

so in one column, you will have each goal for each date and in total line you will have the total count of goals >=99.5% for each month.

I´m not sure if this will work exactly, but I want you to give an idea, hope it´s usefull

Regards,

Chema

[#3]

Unfortunately, I can't use a pivot table - they want the column added to an existing straight table.

If I create the daily results value in the load script, how would I use it?  I'll look up the syntax for group by.

I picture another table, with only these fields:  Date, TestName, sum(tests), sum(errors), ResultsPct.  Then I could link the field TestName to the main table on testname.  I will have to work on ensuring it's only one field to link (and not, say, test+productname)...

Thanks again!

[chematos]

It would be helpfull if you upload your qv file

[#5]

OK, here's my shot at uploading a sample file.

I want to add the new value in the Actual Vs. Goal table.

The value could be the sum of the last column in the Sample Chart table, but I am stuck at grouping this by month by customer by test number.   I think I know how to use "where month = month(today()-1) " to get month-to-date for previous day.