Alert

[bobbydave]

Hi guys,

I've attached a qvw.

I am trying to create an alert that will do the following

data from 2016-10

sum( {2016-10data }) [record count])

and then i am doing an average of the 6 months before

=Sum( {<

     [Fiscal Year Month]= {">=$(=Date(AddMonths(Date#([Fiscal Year Month], 'YYYY-MM'), -6), 'YYYY-MM'))

              <=$(=Date(AddMonths(Date#([Fiscal Year Month], 'YYYY-MM'), -1), 'YYYY-MM'))" }

   >}  

   [Record Count]) which will give me Apr-Sept and then divide this by 6 to give me an average.

I will then divide the 2016-10 data by this average.

At the bottom of my front page, you will see my attempt at getting the 6 months of data. I can get the dates Apr and Sept but its not giving me the actual sum ( {<the 6 months>} [record count])

I am then going to build my alert afterwards if i can get this average figure.

Thanks

[sunny_talwar · Accepted solution]

Try this:

=Sum({<Country, [Fiscal Year Month]= {"=Date#(Only({<[Fiscal Year Month]>}[Fiscal Year Month]), 'YYYY-MM') >= AddMonths(Today(), -7) and Date#(Only({<[Fiscal Year Month]>}[Fiscal Year Month]), 'YYYY-MM') <= AddMonths(Today(), -2)"}>} [Record Count])

[sunny_talwar]

I also suggest looking into these thread to fix your Fiscal Year Month which is read as a text right now

Why don’t my dates work?‌

Get the Dates Right

Dates in Set Analysis