KPI that counts the number of times a metric in a table hits a threshold

[maxwallace]

I have a couple of master measures where im calculating differences between fields like number of days between two different date fields and another that calculates the difference in a row count filed minus the above row count field. I want the KPI to basically just tell me "This threshold was hit X times in the last 7 days".

I have tried making the measure on the back end just as a field, and something like this in the KPI and a few others and cannot figure it out.

=Count({<TixDiffFlag = {1}>} TixDiffFlag)

and this

=Count(

  {<

    TixLoadDateDifference = {">1"},

    QlikTixJobLoadDate = {">=$(=Date(Today()-7))<=$(=Date(Today()))"}

  >}

  TixLoadDateDifference

)

Note: We are trying to go away from Vizlib and other libraries ad use only native Qlik charts.

[stevedark]

Hi @maxwallace 

To do this you will need to use the aggr function.

This function allows you to create a virtual table with a list (or single) dimension and then do other calculations over that virtual table. In your case you want to calculate the total for each date and then count the times it goes over.

The resulting expression will be something like:

sum(aggr(if(sum(ValueField) > $(vThreshold), 1, 0), DateField))

Using an aggr function the calculation will still respect selections, so you can select a certain type of product to see if just those go over the threshold. If you want to always check the total values you might instead want to create a separate table in the load script, with the totals by date calculated during the load, buy doing something like this:

DailyTotals:
LOAD
    DateField,
    sum(ValueField) as [Daily Total]
RESIDENT ExistingDataTable
GROUP BY DateField;

Going this route you will only ever have the total value for the day, rather than a subset based on selections.

Hope that makes sense.

Cheers,
Steve