Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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)
I am seeing 0
Oops, I edited the file but forgot to edit the note. Post is now updated.
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)
Sunny, you are my hero!
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?
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)
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'}.
Would you be able to share a sample. Not sure what might not be working
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.