Qlikview Alert

[bobbydave]

Hey All,

So I have an alert that sends an email should sum([Record Count])=0

However my alert sends all countries and a total for all the countries.

Condition:

=if(
if([Control Description]= 'Claim Payment',
      sum({<[Fiscal Year Month] = { '2015-02' } ,
                 [Country Code]={AU, BE, CO, DK, EE, FI, FR, DE, GR, IE, IT, KR, LT, LV, MX,  NL, NI, NO, PL, PT, ES, SE, CH, TK, UK} >}
//if Case Measures = 1, email which country has 1 
      [Record Count]=0)),
Country
)

Alert:

=if(
if([Control Description]= 'Claim Payment',
sum({<[Fiscal Year Month] = { '2015-02' } ,
[Country Code]={AU, BE, CO, DK, EE, FI, FR, DE, GR, IE, IT, KR, LT, LV, MX,
NL, NI, NO, PL, PT, ES, SE, CH, TK, UK} >}
//if Case Measures = 1, email which country has 1 
  [Record Count]=0)),
Concat(Distinct Country,',') & ': Claim Payment is reported as ' & sum([Record Count]), null()
)

Email:

Austria,Belgium,Colombia,Denmark,Estonia,Finland,France,Germany,Greece,Ireland,Italy,Korea,Latvia,Lithuania,Mexico,Netherlands,Northern Ireland,Norway,Poland,Portugal,Spain,Sweden,Switzerland,Turkey,UK: is reported as 26038

I want it to loop through and only show me values of 0 and alert on these. Say if Latvia was 0, an email should come to me saying Latvia is reported as 0.

Any ideas.

[#2 · Accepted solution]

Try with AGGR function to get list of countries with 0 value.

Alert:

IF( SUM(IF(Aggr(Sum({<[Control Description]={"Claim Payment"},[Fiscal Year Month] = {"2015-02" }>}Record_Count), Country)=0,1,0) > 0 ,

// Email

Concat(IF(Aggr(Sum({<[Control Description]={"Claim Payment"},[Fiscal Year Month] = {"2015-02" }>}Record_Count), Country)=0 , Country ) , ',' )