Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
How do you change your expression each month? Add one month?
Yes, I changed from Month={">6"} to ={">7"}
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?
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.
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)
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.
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.
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)
Thank you, this worked, how can I convert YRMON as date?