Future Date Calculation using Set Analysis

[cbaqir]

In the attached App, if you select FY16, you will see in GRAPH A: FY Estimate to Complete: $0

The expression is: if(GetSelectedCount(FISCAL_YEAR)=0 ,0, round(Sum({1<FCST_DATE = {"$(=date(max(FCST_DATE), 'MM-DD-YYYY'))"}>}FCST_AMT),1000)/1000)

If FY17 is selected, that number is 0 but it should be $40k.

What am I missing?

[sunny_talwar]

I am seeing 0

[cbaqir]

Oops, I edited the file but forgot to edit the note. Post is now updated.

[sunny_talwar · Accepted solution]

Try this:

=if(max(APP_DATE)< max({1}FCST_DATE)+1, 0, round(Sum({1<FCST_DATE = {"$(=date(max(FCST_DATE), 'MM-DD-YYYY'))"}>}FCST_AMT), 1000)/1000)

[cbaqir]

Sunny, you are my hero!

[cbaqir]

I need to include IS_CUR_FCST = {'y'} in the expression but this doesn't seem to be working:

=if(max(APP_DATE)< max({1}FCST_DATE)+1, 0, round(Sum({1<FCST_DATE = {"$(=date(max(FCST_DATE), 'MM-DD-YYYY'))"},IS_CUR_FCST = {'y'}>}FCST_AMT), 1000)/1000)

I assume this is because of the 1 saying to use the entire data set. For FY Estimate to Complete, it should show the sum of future FCST_AMT where IS_CUR_FCST = y but only for the selected FY. Is that possible?

[sunny_talwar]

May be this:

=If(Max({<IS_CUR_FCST = {'y'}>}APP_DATE) < Max({1<IS_CUR_FCST = {'y'}>}FCST_DATE)+1, 0, Round(Sum({1<FCST_DATE = {"$(=Date(Max({<IS_CUR_FCST = {'y'}>}FCST_DATE), 'MM-DD-YYYY'))"}, IS_CUR_FCST = {'y'}>}FCST_AMT), 1000)/1000)

[cbaqir]

It makes sense in theory but...

It's still showing me the sum(FCST_AMT) of IS_CUR_FCST = {'y'} and IS_CUR_FCST = {'n'}.

[sunny_talwar]

Would you be able to share a sample. Not sure what might not be working

[cbaqir]

I've attached a sample with a subset of data. I noticed that now the Lifetime Estimate to Complete isn't working though. Not sure why.

I really appreciate your help. This has been puzzling me for a week and my number 1 priority.

So need  Lifetime Estimate to Complete and  FY Estimate to Complete to work.