Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table that contains a Dimension called Portfolio and an Expression called ETC.
ETC: round(Sum({$<FISCAL_YEAR=,APP_DATE={">$(=Max({<ACT_AMT={'>0'}>}APP_DATE))"}, FCST_DATE = {"$(=date(max(FCST_DATE), 'MM-DD-YYYY'))"}>}FCST_AMT), 1000)/1000
The idea is to either select one Portfolio to focus on or dill down into the Projects that roll up to the Portfolio.
In one example, Program-T should a value of 70 for the ETC expression when no filters are selected. However, clicking on Program-T in the Dimension field or from a list box causes the 70 to disappear.
In the project view, I can see the row with the 70 for ETC when it's not filtered but it disappears when the Program is selected.
Any thoughts?
What if you try this:
Round(Sum({$<FISCAL_YEAR=,APP_DATE={">$(=Max({<ACT_AMT={'>0'}, Program>}APP_DATE))"}, FCST_DATE = {"$(=date(max({<Program>}FCST_DATE), 'MM-DD-YYYY'))"}>}FCST_AMT), 1000)/1000
What if you try this:
Round(Sum({$<FISCAL_YEAR=,APP_DATE={">$(=Max({<ACT_AMT={'>0'}, Program>}APP_DATE))"}, FCST_DATE = {"$(=date(max({<Program>}FCST_DATE), 'MM-DD-YYYY'))"}>}FCST_AMT), 1000)/1000
Thanks, my friend. Can you please explain to me the thought behind that? It seems to work - just testing it.
I think the issue is that when you select a program, the Max(APP_DATE) and/or Max(FCST_DATE) change with them and then one of those goes to 0 and you don't see anything. When you add {<Program>} you are telling the two Maxs to ignore any selection made in Program field.
Thank you! I learn new stuff every day.
I am glad you do because I learn new stuff everyday too