Set Analysis Syntax

cbaqir — Tue, 09 Aug 2016 22:02:18 GMT

See attached example.

The Lifetime Expressions in GRAPH A should not change even if a Fiscal Year selection is made. The numbers should always read:

Lifetime Actuals: $881

Lifetime Estimate to Complete: $40

Lifetime Estimate at Completion: $921

Lifetime Funding: $0

Lifetime Variance to Funding: ($921)

The numbers look right when no Fiscal Year is selected or if FY17 is selected. They are NOT correct when FY15 or FY16 is showing. Any idea how to modify the expression?

Lifetime Estimate to Complete=round(sum({<FCST_DATE = {"$(=date(max(FCST_DATE), 'MM-DD-YYYY'))"},IS_CUR_FCST = {'Y'}>} FCST_AMT), 1000)/1000

I thought it would be the following but then the FY17 Lifetime Estimate to Complete shows 0?????

Lifetime Estimate to Complete=round(Sum({$<FISCAL_YEAR=,MONTH_1=,APP_DATE={">$(=Max({<ACT_AMT={'>0'}>}APP_DATE))"}, IS_CUR_FCST={'y'}>}FCST_AMT), 1000)/1000

he FY expressions in GRAPH A should change based on Fiscal Year Selections. All FY numbers should be blank if one FY is not selected.

CORRECT FY17:

FY Actuals: $0

FY Estimate to Complete: $40

FY Estimate At Completion: $40

FY Budget: $284

FY Variance to Budget: $244

Re: Set Analysis Syntax

sunny_talwar — Tue, 09 Aug 2016 22:55:42 GMT

Not sure I understand your second issue. Check the attached file for the first issue:

=Round(Sum({<FCST_DATE = {"$(=Date(Max({<FISCAL_YEAR>} FCST_DATE), 'MM-DD-YYYY'))"}, IS_CUR_FCST = {'Y'}, FISCAL_YEAR>} FCST_AMT), 1000)/1000

Re: Set Analysis Syntax

cbaqir — Wed, 10 Aug 2016 14:39:53 GMT

That works. Thanks Sunny. I'm not sure I understand why but I am happy it does!

topic Re: Set Analysis Syntax in QlikView

Set Analysis Syntax

Re: Set Analysis Syntax

Re: Set Analysis Syntax