How to filter automatically a chart or table?

[Anonymous]

Hi,

I will like to display a chart for last 6 months, 12 months, 3 months, I have performed this manually:

=Count({$<YR_Cal={2016}, Month={">06"} >} AutoID)

Each month, I should update this expression. How can I update the expression to have the same result, each month automatically, I mean that I don't need to update the expression and it will be calculated automatically.

I have fields as YYYY-MM, Fiscal Year-Quarter (FYYYYY-Q#), created date (full date).

Thank's in advanced for your help.

[sunny_talwar]

How do you change your expression each month? Add one month?

[Anonymous]

Yes, I changed from Month={">6"} to ={">7"}

[sunny_talwar]

Is this based on current month or some other logic? So, since right now we are in December, would you use 12 or another number?

[Gysbert_Wassenaar]

What do you want it to show? It can't show three periods (3, 6 and 12 months) at the same time.

Perhaps something like this works for you:

{<VR_Cal=,Month=,[created date]={">=$(=Date(MonthStart(Max([created date]),-5),'YYYY-MM-DD'))"}>}
Replace the date format YYYY-MM-DD with whatever date format the field [created date] has.

[Anonymous]

btw what is the field name with format      "YYYY-MM"  in your table

Assuming the field name with format  "YYYY-MM"  is MonthYear .

for six months

          =Count({$<YR_Cal={2016},[MonthYear]={">=$(=Date(AddMonths(Max(MonthYear), -6), 'YYYY-MM'))"}>} AutoID)

for 12 months

          =Count({$<YR_Cal={2016},[MonthYear]={">=$(=Date(AddMonths(Max(MonthYear), -12), 'YYYY-MM'))"}>} AutoID)

[Anonymous]

I have the field YRMON, this field stored the data as '2016-12' for December this year. As I cannot use it as it's in the expressions I save the values in two additional fields YR_Cal = 2016 and Month = 12 in this example.

In the expression I am displaying last 6 months, so it's:

=Count({$<YR_Cal={2016}, Month={">06"} >} AutoID)

What I want it's that always displays the last 6 months, but when the year is done this expression won't work, because month will be 01, so the second part of the expression won't take it.

How can I use my YRMON field to (YYYY-MM) in those conditions?

Please advise.

[Anonymous]

No, I will show the periods in three different charts, but the logic to automatic display last 3, 6 or 12 months will be the same.

[sunny_talwar · Accepted solution]

May be like this:

If you YRMON field is not read as date

=Count({$<AutoID = {"=Date#(YRMON, 'YYYY-MM') > MonthStart(Today(), -6)"}>} AutoID)

If YRMON is read as date

=Count({$<YRMON= {"$(='>' & Date(MonthStart(Today(), -6), 'YYYY-MM'))"}>} AutoID)

[Anonymous]

Thank you, this worked, how can I convert YRMON as date?