Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all,
I am trying to change an expression that previously looked at a date range up to three months prior. I now need to change it to show data prior to 90 days. Below is the expression I have developed, but the output I receive in my chart is "-". The font in red is what I have added to change the max date to 90 days. Previously, the red text was Date(MonthEnd(Max(MonthYear), -3).
Num(count(DISTINCT{1< [Site]={'Site Reviewed On Time', 'Site Reviewed Late'}, MonthYear={"$(='>=' & Date(MonthStart(Max(MonthYear), -13), 'MMM-YYYY') & '<=' & Date(=Date(today(), -90), 'MMM-YYYY')"}, [Record Status] -={'Postponed', 'Cancelled'}>} RPA)/count(DISTINCT{1< [Site]-={''}, MonthYear={"$(='>=' & Date(MonthStart(Max(MonthYear), -13), 'MMM-YYYY') & '<=' & Date(=Date(today(), -90)), 'DD-MM-YYYY')"}, [Record Status] -={'Postponed', 'Cancelled'}>} RPA),'#,##0%')
Any help would be appreciated!
Thank you
Dylan
Perhaps this?
Num(count(DISTINCT{1< [Site]={'Site Reviewed On Time', 'Site Reviewed Late'},MonthYear={">=$(=Date(MonthStart(AddMonths(Max(MonthYear), -13)), 'MMM-YYYY'))<=$(=Date(MonthStart(AddMonths(Max(MonthYear), -3)), 'MMM-YYYY'))"}, [Record Status] -={'Postponed', 'Cancelled'}>} RPA)/count(DISTINCT{1< [Site]-={''}, MonthYear={">=$(=Date(MonthStart(AddMonths(Max(MonthYear), -13)), 'MMM-YYYY'))<=$(=Date(MonthStart(AddMonths(Max(MonthYear), -3)), 'MMM-YYYY'))"}, [Record Status] -={'Postponed', 'Cancelled'}>} RPA),'#,##0%')
Perhaps this?
Num(count(DISTINCT{1< [Site]={'Site Reviewed On Time', 'Site Reviewed Late'},MonthYear={">=$(=Date(MonthStart(AddMonths(Max(MonthYear), -13)), 'MMM-YYYY'))<=$(=Date(MonthStart(AddMonths(Max(MonthYear), -3)), 'MMM-YYYY'))"}, [Record Status] -={'Postponed', 'Cancelled'}>} RPA)/count(DISTINCT{1< [Site]-={''}, MonthYear={">=$(=Date(MonthStart(AddMonths(Max(MonthYear), -13)), 'MMM-YYYY'))<=$(=Date(MonthStart(AddMonths(Max(MonthYear), -3)), 'MMM-YYYY'))"}, [Record Status] -={'Postponed', 'Cancelled'}>} RPA),'#,##0%')
Hello Anil,
Where you stated '"<=$(=Date(MonthStart(AddYears(Max(MonthYear), -3))" I am actually trying to make it reflect 90 days and no longer 3 months. Sorry my explanation in the question may have been a little misleading. Do you know how I would change your above expression to reflect 90 days prior?
What is your date field and how it is? Today() is not the correct one, IF you calculate MonthYear from DateField - You can try? But actually you should be having similar functionality when you compare MonthYear with Date so, I would suggest to use DateField instead MonhtYear. If not, Please explain more with some dataset ..
<=$(Date(Max(DateField, -30)))