Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
j_schmidt
Creator
Creator

SET ANALYSIS | Within last 24 hours

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

1 Solution

Accepted Solutions
sunny_talwar

Try something like this

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

View solution in original post

14 Replies
jyothish8807
Master II
Master II

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])

Best Regards,
KC
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?

j_schmidt
Creator
Creator
Author

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.

j_schmidt
Creator
Creator
Author

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

j_schmidt
Creator
Creator
Author

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.

j_schmidt
Creator
Creator
Author

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?