Set Analysis Syntax

[cbaqir]

See attached example.

The Lifetime Expressions in GRAPH A should not change even if a Fiscal Year selection is made. The numbers should always read:

Lifetime Actuals: $881

Lifetime Estimate to Complete: $40

Lifetime Estimate at Completion: $921

Lifetime Funding: $0

Lifetime Variance to Funding: ($921)

The numbers look right when no Fiscal Year is selected or if FY17 is selected. They are NOT correct when FY15 or FY16 is showing. Any idea how to modify the expression?

Lifetime Estimate to Complete=round(sum({<FCST_DATE = {"$(=date(max(FCST_DATE), 'MM-DD-YYYY'))"},IS_CUR_FCST = {'Y'}>} FCST_AMT), 1000)/1000 

I thought it would be the following but then the FY17 Lifetime Estimate to Complete shows 0?????

Lifetime Estimate to Complete=round(Sum({$<FISCAL_YEAR=,MONTH_1=,APP_DATE={">$(=Max({<ACT_AMT={'>0'}>}APP_DATE))"}, IS_CUR_FCST={'y'}>}FCST_AMT), 1000)/1000

he FY expressions in GRAPH A should change based on Fiscal Year Selections. All FY numbers should be blank if one FY is not selected.

CORRECT FY17:

FY Actuals: $0

FY Estimate to Complete: $40

FY Estimate At Completion: $40

FY Budget: $284

FY Variance to Budget: $244

[sunny_talwar · Accepted solution]

Not sure I understand your second issue. Check the attached file for the first issue:

=Round(Sum({<FCST_DATE = {"$(=Date(Max({<FISCAL_YEAR>} FCST_DATE), 'MM-DD-YYYY'))"}, IS_CUR_FCST = {'Y'}, FISCAL_YEAR>} FCST_AMT), 1000)/1000

[cbaqir]

That works. Thanks Sunny. I'm not sure I understand why but I am happy it does!