Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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)
Ideas for what exactly? Is the weekly alert not working?
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?
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)
that worked