Fiscal Year Month - 1

[bobbydave]

I have an alert and what I am trying to do is show Fiscal Year Month as the previous month.

2016-03 should appear as 2016-02.

sum(
if(
aggr(sum({<[Control Description]={"Policy New Business, Policy Cancellations"},
[Fiscal Year Month] = {"2016-02" }>}
[Record Count]),
Country)=0,
1,0)) > 0

above is the alert I am using.

My alert should return ONLY countries that have a record count of zero.

Any help appreciated

[sunny_talwar]

You want to make the Fiscal Year Month dynamic? Is that what you goal is? Also please fix your set analysis for Control Description

Sum(If(Aggr(Sum({<[Control Description] = {'Policy New Business', 'Policy Cancellations'}, [Fiscal Year Month] = {"2016-02" }>} [Record Count]), Country) = 0, 1, 0))

[bobbydave]

You've corrected my expression but its within the set analysis which is my issue.

I don't want to have 2016-02 showing.

I want it to be dynamic. I would like to create something that picks up Fiscal Year Month which is currently 2016-03 and brings it back to Feb (2016-02) using 'expression -1.

I've tried using

sum.....

     if(....

               [Fiscal Year Month] = {$(monthstart(month([Fiscal Year Month]), -1))),

    .......

     1,0)

[sunny_talwar · Accepted solution]

May be something like this:

Sum(If(Aggr(Sum({<[Control Description] = {'Policy New Business', 'Policy Cancellations'}, [Fiscal Year Month] = {"$(=Date(AddMonths(Max([Fiscal Year Month]), -1), 'YYYY-MM'))"}>} [Record Count]), Country) = 0, 1, 0))