SET ANALYSIS | Within last 24 hours

[schmidtj]

Hi,

i would like to build an KPI which gives me a count of data (tasks) where the last failed reload time is newer than 24 hours. 

My KPI of last reload failure is: timestamp(Max({<[Reload Failure]={1}>} [Reload Finish]))

My problem is now to buold that KPI:

Count
(
  {
    <
      [Reload Failure]={1},

      # ? Last reload failure within last 24 hours ? #
    >
  }

  [Task App Name]
)

Anybody an idea how to do that with SET ANALYSIS?

Thanks,

John

[jyothish8807]

Hi  J,

Try like this:

Create two variables:

v24 =timestamp(now()-1);

Build you max reload time in the backed for  more convenience and better performance like this:

MaxReloadTime:

Load

[Task App Name], //Keep your key to link to your existing data model

timestamp(Max([Reload Finish]) )    as [Max Reload Finish]   //Make sure this filed is in same format as 'v24' variable output

resident  table

where [Reload Failure]= 1

group by [Task App Name];

Then create a expression like this:

Count({<[Max Reload Finish] ={">=$(v24 )"}>}[Task App Name])

[sunny_talwar]

What exactly are you trying to count here? last reload would be associated with a single task, right? So, count would always be 1? Am I missing something?

[schmidtj]

Hi.

i'm trying to count the tasks, which havent finished successfully within the last 24 hours.

I'm using a copy from the app 'Operations Monitor' from Qlik.

[schmidtj]

Hi,

thanks for the reply.

I tried your solution but couldnt get it to work.

Maybe its a too complicated task for my skill level.

I'm using a copy from the app 'Operations Monitor' from Qlik.

Adapting the model there as you suggested is a bit too advanced for me i'm afraid.

I tried this, which is not working:

Count
(
{
<
num(timestamp(Max({<[Reload Failure]={1}>} [Reload Finish]))) = {">=$(vLast24Hours)"}
>
}

[Task App Name]
)

i'm using Num() for comparison, the variable is also calculated with num().

The result is a '-' so i guess no number has been calculated.

[sunny_talwar]

May be this

Count({<[Reload Failure] = {1}, [Reload Finish] = {">=$(=TimeStamp(Now(1) - 1))"}>} [Task App Name])

Basically, look for all the reload which finished between 1 day from now and now

[schmidtj]

Thanks for the reply.

Your code works but its not exactly what i need. It counts the failures within the last day. The problem is, i the task is fixed and rerun successfully, i still have that failed task-run in my KPI. Therefore i nudd to count the MAX(successfull execution time) from each task and count those which haven non within the last 24 hours.

[sunny_talwar]

I have a general idea of what you need (set analysis is probably not going to work for you), but not sure how to give you an exact expression without seeing your data and trying it out.

[schmidtj]

Yes i understand.

Basically my data is the system app from Qlik Sense 'Operations Monitor'.

The data there is quite complex.

[sunny_talwar]

I understand, all I want is few rows of data and explain what you are looking to get?