Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

bobbydave
Contributor 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?

Tags (1)
1 Solution

Accepted Solutions

Re: Alert

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

Re: Alert

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

bobbydave
Contributor III

Re: Alert

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?

Re: Alert

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

bobbydave
Contributor III

Re: Alert

that worked