topic Re: Alert in QlikView

Alert

bobbydave — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Alert

sunny_talwar — Thu, 26 Oct 2017 12:02:20 GMT

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

Re: Alert

bobbydave — Thu, 26 Oct 2017 12:28:28 GMT

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

sunny_talwar — Thu, 26 Oct 2017 12:38:03 GMT

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)

Re: Alert

bobbydave — Thu, 26 Oct 2017 13:05:36 GMT

that worked