Value Disappears?

[cbaqir]

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?

[sunny_talwar · Accepted solution]

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

[cbaqir]

Thanks, my friend. Can you please explain to me the thought behind that? It seems to work - just testing it.

[sunny_talwar]

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.

[cbaqir]

Thank you! I learn new stuff every day.

[sunny_talwar]

I am glad you do because I learn new stuff everyday too