topic Value Disappears? in QlikView

Value Disappears?

cbaqir — Fri, 19 Feb 2016 19:22:02 GMT

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?

Re: Value Disappears?

sunny_talwar — Fri, 19 Feb 2016 19:24:21 GMT

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

Re: Value Disappears?

cbaqir — Fri, 19 Feb 2016 19:28:18 GMT

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

Re: Value Disappears?

sunny_talwar — Fri, 19 Feb 2016 19:30:19 GMT

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.

Re: Value Disappears?

cbaqir — Fri, 19 Feb 2016 19:31:00 GMT

Thank you! I learn new stuff every day.

Re: Value Disappears?

sunny_talwar — Fri, 19 Feb 2016 19:31:46 GMT

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