Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

camjvine18
Contributor

Count and aggregation of variable for the day

So I need to assess if a breach has occurred or not occurred on a day. There are a number of variables processed each day, if only one of them Breaches I need to count that as the breach for the day, ie. 1. It is a binary solution, if it is not a breach it must have been ontime. What I attempted to do with the formula is find the minimum of minutes for the day based on the unique ID which is the feedId, and if less than 0, Breach, if not Ontime.


Count(

aggr(

      if(           

              min(Total <Status,Category> Minutes)<0,'Breach','Ontime'

             )           

            ,Status,Category,Minutes

         ) 

    )


In the screenshot below the Count for day should be 1 for Breach and 0 for Ontime

Capture.PNG

2 Replies
vinieme12
Esteemed Contributor II

Re: Count and aggregation of variable for the day

Try below

Pick(Match(Status,'Breach','Ontime')

,

Sum(AGGR(if(min(Total <Milestone> Minutes)<0,1,0),Milestone))

,

Sum(AGGR(if(min(Total <Milestone> Minutes)<0,0,1),Milestone))

)

camjvine18
Contributor

Re: Count and aggregation of variable for the day

That did not work. It needs to work on a consolidated basis as well, there are 18 days that were either in breach or not that is not occurring. If I choose one day it should be either in breach or not for category = source so 1 or 0.

This is how it should look like. This report is already at an aggregated level from the DB, I want to aggregate in Qlik from detailed information.

Capture.PNG

This is how it should look when I choose one day.

Capture.PNG

Community Browser