Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bobbydave
Creator III
Creator III

Alert

I've created an alert that returns true and an alert is generated, monthly

= //Result - Last month divided by the sum of the previous 6 months before last month
if(sum(aggr(if( (Sum({< Country -= {'Korea', 'Latvia', 'Northern Ireland', 'Austria', 'Estonia'}, [Monthly Control Description] -= {'Avaya'},
       [Fiscal Year Month] = {"$(=Date(AddMonths(Max(Today()), -1), 'YYYY-MM'))"}>}
     [Monthly Record Count])/
Sum(
  {<[The Fiscal Year Month] =
  {">=$(=Date(AddMonths(Max(Today()),-8), 'YYYY-MM'))<=$(=Date(AddMonths(Max(Today()),-2), 'YYYY-MM'))"},
  Country -= {'Korea', 'Latvia', 'Northern Ireland',  'Austria', 'Estonia'}, [Monthly Control Description] -= {'Avaya'}>}
  [Monthly Record Count]))

  // is greater than 0.2
  > 0.25,1,0),[Monthly Control Description],Country))>0,1,0)

// this should only return anything over 0.2

Now I am trying to do a weekly alert that creates an alert if true.

= //Result - Last week divided by the sum of the previous 6 weeks before last week

= //Result
if(sum(aggr(if( (

//Last week
  sum(
  {<
  Country -= {'Korea', 'Latvia', 'Northern Ireland'},
[Weekly Control Description] -= {'Avaya'},
  Fiscal_Year_Week ={"$(=replace(Weekname(Today()-7), '/', '-'))"}
  >}

[Weekly Record Count])
    
     /
    
// Sum of the last 6 individual weeks    
sum(
  {<
  Country -= {'Korea', 'Latvia', 'Northern Ireland'},
[Weekly Control Description] -= {'Avaya'},
  Fiscal_Year_Week ={"$(=replace(Weekname(Today()-56), '/', '-'))"}
  >}

[Weekly Record Count])

+

sum(
  {<
  Country -= {'Korea', 'Latvia', 'Northern Ireland'},
[Weekly Control Description] -= {'Avaya'},
  Fiscal_Year_Week ={"$(=replace(Weekname(Today()-49), '/', '-'))"}
  >}

[Weekly Record Count])

+

  sum(
  {<
  Country -= {'Korea', 'Latvia', 'Northern Ireland'},
[Weekly Control Description] -= {'Avaya'},
  Fiscal_Year_Week ={"$(=replace(Weekname(Today()-42), '/', '-'))"}
  >}

[Weekly Record Count])

+

  sum(
  {<
  Country -= {'Korea', 'Latvia', 'Northern Ireland'},
[Weekly Control Description] -= {'Avaya'},
  Fiscal_Year_Week ={"$(=replace(Weekname(Today()-35), '/', '-'))"}
  >}

[Weekly Record Count])

+

  sum(
  {<
  Country -= {'Korea', 'Latvia', 'Northern Ireland'},
[Weekly Control Description] -= {'Avaya'},
  Fiscal_Year_Week ={"$(=replace(Weekname(Today()-28), '/', '-'))"}
  >}

[Weekly Record Count])

+

  sum(
  {<
  Country -= {'Korea', 'Latvia', 'Northern Ireland'},
[Weekly Control Description] -= {'Avaya'},
  Fiscal_Year_Week ={"$(=replace(Weekname(Today()-21), '/', '-'))"}
  >}

[Weekly Record Count])

+

  sum(
  {<
  Country -= {'Korea', 'Latvia', 'Northern Ireland'},
[Weekly Control Description] -= {'Avaya'},
  Fiscal_Year_Week ={"$(=replace(Weekname(Today()-14), '/', '-'))"}
  >}

[Weekly Record Count])

  // is greater than 0.2
  > 0.25,1,0),[Monthly Control Description],Country))>0,1,0))

// this should only return anything over 0.25

Any one with any ideas?

1 Solution

Accepted Solutions
sunny_talwar

May be this

If(Sum(Aggr(If(

Sum({<Country -= {'Korea', 'Latvia', 'Northern Ireland'}, [Weekly Control Description] -= {'Avaya'}, Fiscal_Year_Week ={"$(=Replace(Weekname(Today()-7), '/', '-'))"}>} [Weekly Record Count])
/
(Sum({<Country -= {'Korea', 'Latvia', 'Northern Ireland'}, [Weekly Control Description] -= {'Avaya'}, Fiscal_Year_Week ={"$(=replace(Weekname(Today()-56), '/', '-'))"}>} [Weekly Record Count])

+

Sum({<Country -= {'Korea', 'Latvia', 'Northern Ireland'}, [Weekly Control Description] -= {'Avaya'}, Fiscal_Year_Week ={"$(=replace(Weekname(Today()-49), '/', '-'))"}>} [Weekly Record Count])

+

Sum({<Country -= {'Korea', 'Latvia', 'Northern Ireland'}, [Weekly Control Description] -= {'Avaya'}, Fiscal_Year_Week ={"$(=replace(Weekname(Today()-42), '/', '-'))"}>}[Weekly Record Count])

+

Sum({<Country -= {'Korea', 'Latvia', 'Northern Ireland'}, [Weekly Control Description] -= {'Avaya'}, Fiscal_Year_Week ={"$(=replace(Weekname(Today()-35), '/', '-'))"}>} [Weekly Record Count])

+

Sum({<Country -= {'Korea', 'Latvia', 'Northern Ireland'}, [Weekly Control Description] -= {'Avaya'}, Fiscal_Year_Week ={"$(=replace(Weekname(Today()-28), '/', '-'))"}>} [Weekly Record Count])

+

Sum({<Country -= {'Korea', 'Latvia', 'Northern Ireland'}, [Weekly Control Description] -= {'Avaya'}, Fiscal_Year_Week ={"$(=replace(Weekname(Today()-21), '/', '-'))"}>} [Weekly Record Count])

+

Sum({<Country -= {'Korea', 'Latvia', 'Northern Ireland'}, [Weekly Control Description] -= {'Avaya'}, Fiscal_Year_Week ={"$(=replace(Weekname(Today()-14), '/', '-'))"}>} [Weekly Record Count])) > 0.25,1, 0), [Monthly Control Description], Country)) > 0, 1, 0)

View solution in original post

4 Replies
sunny_talwar

Ideas for what exactly? Is the weekly alert not working?

bobbydave
Creator III
Creator III
Author

The first alert returns true but either i have coded the weekly alert incorrectly as it returns false and i've more or less replaced the last month with code for last week and the last 6 months with the last 6 weeks code. So i'd imagine it would return true also. I am wondering have i a bracket in the wrong place or have i wrote the code correctty?

sunny_talwar

May be this

If(Sum(Aggr(If(

Sum({<Country -= {'Korea', 'Latvia', 'Northern Ireland'}, [Weekly Control Description] -= {'Avaya'}, Fiscal_Year_Week ={"$(=Replace(Weekname(Today()-7), '/', '-'))"}>} [Weekly Record Count])
/
(Sum({<Country -= {'Korea', 'Latvia', 'Northern Ireland'}, [Weekly Control Description] -= {'Avaya'}, Fiscal_Year_Week ={"$(=replace(Weekname(Today()-56), '/', '-'))"}>} [Weekly Record Count])

+

Sum({<Country -= {'Korea', 'Latvia', 'Northern Ireland'}, [Weekly Control Description] -= {'Avaya'}, Fiscal_Year_Week ={"$(=replace(Weekname(Today()-49), '/', '-'))"}>} [Weekly Record Count])

+

Sum({<Country -= {'Korea', 'Latvia', 'Northern Ireland'}, [Weekly Control Description] -= {'Avaya'}, Fiscal_Year_Week ={"$(=replace(Weekname(Today()-42), '/', '-'))"}>}[Weekly Record Count])

+

Sum({<Country -= {'Korea', 'Latvia', 'Northern Ireland'}, [Weekly Control Description] -= {'Avaya'}, Fiscal_Year_Week ={"$(=replace(Weekname(Today()-35), '/', '-'))"}>} [Weekly Record Count])

+

Sum({<Country -= {'Korea', 'Latvia', 'Northern Ireland'}, [Weekly Control Description] -= {'Avaya'}, Fiscal_Year_Week ={"$(=replace(Weekname(Today()-28), '/', '-'))"}>} [Weekly Record Count])

+

Sum({<Country -= {'Korea', 'Latvia', 'Northern Ireland'}, [Weekly Control Description] -= {'Avaya'}, Fiscal_Year_Week ={"$(=replace(Weekname(Today()-21), '/', '-'))"}>} [Weekly Record Count])

+

Sum({<Country -= {'Korea', 'Latvia', 'Northern Ireland'}, [Weekly Control Description] -= {'Avaya'}, Fiscal_Year_Week ={"$(=replace(Weekname(Today()-14), '/', '-'))"}>} [Weekly Record Count])) > 0.25,1, 0), [Monthly Control Description], Country)) > 0, 1, 0)

bobbydave
Creator III
Creator III
Author

that worked