Simplify expression

[#1]

Hi,

I was wondering if someone could tell me if there is an easier way to do this expression, it gives me the result I need, but it seems to have slowed the document down when making selections.

I have a field in my data which specifies how many months back i need to go, so if the field is -3 and they have selected June 2013, then I need to be showing the data for Mar 2013.

Hope this makes sense.

Cheers

=

IF(Data.DataDisplayQtr = 1,
IF(Data.QtrReportAdj = 0,
IF(Data.NumberFormat='%',NUM(ONLY({$<Data.ReportMonth1=, [Report Calendar.FinancialYear]=, MonthYear={"$(=Text(Date(AddMonths(Date#(vSelectedReportMonthDate,'MMM YYYY'),0),'MMM YYYY')))"}>}Data.QuarterActual),'0.00%'),
IF(Data.NumberFormat='0',NUM(ONLY({$<Data.ReportMonth1=, [Report Calendar.FinancialYear]=, MonthYear={"$(=Text(Date(AddMonths(Date#(vSelectedReportMonthDate,'MMM YYYY'),0),'MMM YYYY')))"}>}Data.QuarterActual),'0'),
IF(Data.NumberFormat='0.00_',NUM(ONLY({$<Data.ReportMonth1=, [Report Calendar.FinancialYear]=, MonthYear={"$(=Text(Date(AddMonths(Date#(vSelectedReportMonthDate,'MMM YYYY'),0),'MMM YYYY')))"}>}Data.QuarterActual),'0.00%'),
IF(Data.NumberFormat='hh:mm',TIME(ONLY({$<Data.ReportMonth1=, [Report Calendar.FinancialYear]=, MonthYear={"$(=Text(Date(AddMonths(Date#(vSelectedReportMonthDate,'MMM YYYY'),0),'MMM YYYY')))"}>}Data.QuarterActual),'hh:mm'),
IF(Data.NumberFormat='Text',ONLY({$<Data.ReportMonth1=, [Report Calendar.FinancialYear]=, MonthYear={"$(=Text(Date(AddMonths(Date#(vSelectedReportMonthDate,'MMM YYYY'),0),'MMM YYYY')))"}>}Data.QuarterActual),
IF(Data.NumberFormat='Rag','',ONLY({$<Data.ReportMonth1=, [Report Calendar.FinancialYear]=, MonthYear={"$(=Text(Date(AddMonths(Date#(vSelectedReportMonthDate,'MMM YYYY'),0),'MMM YYYY')))"}>}Data.QuarterActual)
)))))),

IF(Data.QtrReportAdj = -3,
IF(Data.NumberFormat='%',NUM(ONLY({$<Data.ReportMonth1=, [Report Calendar.FinancialYear]=, MonthYear={"$(=Text(Date(AddMonths(Date#(vSelectedReportMonthDate,'MMM YYYY'),-3),'MMM YYYY')))"}>}Data.QuarterActual),'0.00%'),
IF(Data.NumberFormat='0',NUM(ONLY({$<Data.ReportMonth1=, [Report Calendar.FinancialYear]=, MonthYear={"$(=Text(Date(AddMonths(Date#(vSelectedReportMonthDate,'MMM YYYY'),-3),'MMM YYYY')))"}>}Data.QuarterActual),'0'),
IF(Data.NumberFormat='0.00_',NUM(ONLY({$<Data.ReportMonth1=, [Report Calendar.FinancialYear]=, MonthYear={"$(=Text(Date(AddMonths(Date#(vSelectedReportMonthDate,'MMM YYYY'),-3),'MMM YYYY')))"}>}Data.QuarterActual),'0.00%'),
IF(Data.NumberFormat='hh:mm',TIME(ONLY({$<Data.ReportMonth1=, [Report Calendar.FinancialYear]=, MonthYear={"$(=Text(Date(AddMonths(Date#(vSelectedReportMonthDate,'MMM YYYY'),-3),'MMM YYYY')))"}>}Data.QuarterActual),'hh:mm'),
IF(Data.NumberFormat='Text',ONLY({$<Data.ReportMonth1=, [Report Calendar.FinancialYear]=, MonthYear={"$(=Text(Date(AddMonths(Date#(vSelectedReportMonthDate,'MMM YYYY'),-3),'MMM YYYY')))"}>}Data.QuarterActual),
IF(Data.NumberFormat='Rag','',ONLY({$<Data.ReportMonth1=, [Report Calendar.FinancialYear]=, MonthYear={"$(=Text(Date(AddMonths(Date#(vSelectedReportMonthDate,'MMM YYYY'),-3),'MMM YYYY')))"}>}Data.QuarterActual)
)))))),

IF(Data.QtrReportAdj = -6,
IF(Data.NumberFormat='%',NUM(ONLY({$<Data.ReportMonth1=, [Report Calendar.FinancialYear]=, MonthYear={"$(=Text(Date(AddMonths(Date#(vSelectedReportMonthDate,'MMM YYYY'),-6),'MMM YYYY')))"}>}Data.QuarterActual),'0.00%'),
IF(Data.NumberFormat='0',NUM(ONLY({$<Data.ReportMonth1=, [Report Calendar.FinancialYear]=, MonthYear={"$(=Text(Date(AddMonths(Date#(vSelectedReportMonthDate,'MMM YYYY'),-6),'MMM YYYY')))"}>}Data.QuarterActual),'0'),
IF(Data.NumberFormat='0.00_',NUM(ONLY({$<Data.ReportMonth1=, [Report Calendar.FinancialYear]=, MonthYear={"$(=Text(Date(AddMonths(Date#(vSelectedReportMonthDate,'MMM YYYY'),-6),'MMM YYYY')))"}>}Data.QuarterActual),'0.00%'),
IF(Data.NumberFormat='hh:mm',TIME(ONLY({$<Data.ReportMonth1=, [Report Calendar.FinancialYear]=, MonthYear={"$(=Text(Date(AddMonths(Date#(vSelectedReportMonthDate,'MMM YYYY'),-6),'MMM YYYY')))"}>}Data.QuarterActual),'hh:mm'),
IF(Data.NumberFormat='Text',ONLY({$<Data.ReportMonth1=, [Report Calendar.FinancialYear]=, MonthYear={"$(=Text(Date(AddMonths(Date#(vSelectedReportMonthDate,'MMM YYYY'),-6),'MMM YYYY')))"}>}Data.QuarterActual),
IF(Data.NumberFormat='Rag','',ONLY({$<Data.ReportMonth1=, [Report Calendar.FinancialYear]=, MonthYear={"$(=Text(Date(AddMonths(Date#(vSelectedReportMonthDate,'MMM YYYY'),-6),'MMM YYYY')))"}>}Data.QuarterActual)
)))))),

IF(Data.QtrReportAdj = -9,
IF(Data.NumberFormat='%',NUM(ONLY({$<Data.ReportMonth1=, [Report Calendar.FinancialYear]=, MonthYear={"$(=Text(Date(AddMonths(Date#(vSelectedReportMonthDate,'MMM YYYY'),-9),'MMM YYYY')))"}>}Data.QuarterActual),'0.00%'),
IF(Data.NumberFormat='0',NUM(ONLY({$<Data.ReportMonth1=, [Report Calendar.FinancialYear]=, MonthYear={"$(=Text(Date(AddMonths(Date#(vSelectedReportMonthDate,'MMM YYYY'),-9),'MMM YYYY')))"}>}Data.QuarterActual),'0'),
IF(Data.NumberFormat='0.00_',NUM(ONLY({$<Data.ReportMonth1=, [Report Calendar.FinancialYear]=, MonthYear={"$(=Text(Date(AddMonths(Date#(vSelectedReportMonthDate,'MMM YYYY'),-9),'MMM YYYY')))"}>}Data.QuarterActual),'0.00%'),
IF(Data.NumberFormat='hh:mm',TIME(ONLY({$<Data.ReportMonth1=, [Report Calendar.FinancialYear]=, MonthYear={"$(=Text(Date(AddMonths(Date#(vSelectedReportMonthDate,'MMM YYYY'),-9),'MMM YYYY')))"}>}Data.QuarterActual),'hh:mm'),
IF(Data.NumberFormat='Text',ONLY({$<Data.ReportMonth1=, [Report Calendar.FinancialYear]=, MonthYear={"$(=Text(Date(AddMonths(Date#(vSelectedReportMonthDate,'MMM YYYY'),-9),'MMM YYYY')))"}>}Data.QuarterActual),
IF(Data.NumberFormat='Rag','',ONLY({$<Data.ReportMonth1=, [Report Calendar.FinancialYear]=, MonthYear={"$(=Text(Date(AddMonths(Date#(vSelectedReportMonthDate,'MMM YYYY'),-9),'MMM YYYY')))"}>}Data.QuarterActual)
)))))),

IF(Data.QtrReportAdj = -12,
IF(Data.NumberFormat='%',NUM(ONLY({$<Data.ReportMonth1=, [Report Calendar.FinancialYear]=, MonthYear={"$(=Text(Date(AddMonths(Date#(vSelectedReportMonthDate,'MMM YYYY'),-12),'MMM YYYY')))"}>}Data.QuarterActual),'0.00%'),
IF(Data.NumberFormat='0',NUM(ONLY({$<Data.ReportMonth1=, [Report Calendar.FinancialYear]=, MonthYear={"$(=Text(Date(AddMonths(Date#(vSelectedReportMonthDate,'MMM YYYY'),-12),'MMM YYYY')))"}>}Data.QuarterActual),'0'),
IF(Data.NumberFormat='0.00_',NUM(ONLY({$<Data.ReportMonth1=, [Report Calendar.FinancialYear]=, MonthYear={"$(=Text(Date(AddMonths(Date#(vSelectedReportMonthDate,'MMM YYYY'),-12),'MMM YYYY')))"}>}Data.QuarterActual),'0.00%'),
IF(Data.NumberFormat='hh:mm',TIME(ONLY({$<Data.ReportMonth1=, [Report Calendar.FinancialYear]=, MonthYear={"$(=Text(Date(AddMonths(Date#(vSelectedReportMonthDate,'MMM YYYY'),-12),'MMM YYYY')))"}>}Data.QuarterActual),'hh:mm'),
IF(Data.NumberFormat='Text',ONLY({$<Data.ReportMonth1=, [Report Calendar.FinancialYear]=, MonthYear={"$(=Text(Date(AddMonths(Date#(vSelectedReportMonthDate,'MMM YYYY'),-12),'MMM YYYY')))"}>}Data.QuarterActual),
IF(Data.NumberFormat='Rag','',ONLY({$<Data.ReportMonth1=, [Report Calendar.FinancialYear]=, MonthYear={"$(=Text(Date(AddMonths(Date#(vSelectedReportMonthDate,'MMM YYYY'),-12),'MMM YYYY')))"}>}Data.QuarterActual

)
))))))))))),'')

[iliyansomlev]

Hi,

you can try creating a variable for your expression that uses your months-to-go-back variable ,$(Data.QtrReportAdj, e,g

vExp

=

IF(Data.NumberFormat='hh:mm',TIME(ONLY({$<Data.ReportMonth1=, [Report Calendar.FinancialYear]=, MonthYear={"$(=Text(Date(AddMonths(Date#(vSelectedReportMonthDate,'MMM YYYY'),$(Data.QtrReportAdj)),'MMM YYYY')))"}>}Data.QuarterActual),'hh:mm'),

IF(Data.NumberFormat='Text',ONLY({$<Data.ReportMonth1=, [Report Calendar.FinancialYear]=, MonthYear={"$(=Text(Date(AddMonths(Date#(vSelectedReportMonthDate,'MMM YYYY'),$(Data.QtrReportAdj)),'MMM YYYY')))"}>}Data.QuarterActual),

IF(Data.NumberFormat='Rag','',ONLY({$<Data.ReportMonth1=, [Report Calendar.FinancialYear]=, MonthYear={"$(=Text(Date(AddMonths(Date#(vSelectedReportMonthDate,'MMM YYYY'),$(Data.QtrReportAdj)),'MMM YYYY')))"}>}Data.QuarterActual),

IF(Data.NumberFormat='%',NUM(ONLY({$<Data.ReportMonth1=, [Report Calendar.FinancialYear]=, MonthYear={"$(=Text(Date(AddMonths(Date#(vSelectedReportMonthDate,'MMM YYYY'),$(Data.QtrReportAdj)),'MMM YYYY')))"}>}Data.QuarterActual),'0.00%'),

IF(Data.NumberFormat='0',NUM(ONLY({$<Data.ReportMonth1=, [Report Calendar.FinancialYear]=, MonthYear={"$(=Text(Date(AddMonths(Date#(vSelectedReportMonthDate,'MMM YYYY'),$(Data.QtrReportAdj)),'MMM YYYY')))"}>}Data.QuarterActual),'0'),

IF(Data.NumberFormat='0.00_',NUM(ONLY({$<Data.ReportMonth1=, [Report Calendar.FinancialYear]=, MonthYear={"$(=Text(Date(AddMonths(Date#(vSelectedReportMonthDate,'MMM YYYY'),$(Data.QtrReportAdj)),'MMM YYYY')))"}>}Data.QuarterActual),'0.00%')))))))


and then use the variable expansion in your objects. I haven't tested it and don't know if it will work or save calculation time, but it will simplify your editing/revision later.

Hope this helps,

[#3]

Hi,

Thanks for your sugestion but it didnt work.

Can anyone else help or surgest how I might go about doing it?

I know what date has been selected and howmany months I need to go back, how can I show the data for that particual month?

Cheers