topic Future Date Calculation using Set Analysis in QlikView

Future Date Calculation using Set Analysis

cbaqir — Wed, 03 Aug 2016 19:24:50 GMT

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?

Re: Future Date Calculation using Set Analysis

sunny_talwar — Wed, 03 Aug 2016 19:27:21 GMT

I am seeing 0

Re: Future Date Calculation using Set Analysis

cbaqir — Wed, 03 Aug 2016 19:34:28 GMT

Oops, I edited the file but forgot to edit the note. Post is now updated.

Re: Future Date Calculation using Set Analysis

sunny_talwar — Wed, 03 Aug 2016 20:04:13 GMT

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)

Re: Future Date Calculation using Set Analysis

cbaqir — Wed, 03 Aug 2016 20:06:43 GMT

Sunny, you are my hero!

Re: Future Date Calculation using Set Analysis

cbaqir — Wed, 10 Aug 2016 16:19:21 GMT

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?

Re: Future Date Calculation using Set Analysis

sunny_talwar — Wed, 10 Aug 2016 16:28:17 GMT

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)

Re: Future Date Calculation using Set Analysis

cbaqir — Wed, 10 Aug 2016 16:41:40 GMT

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'}.

Re: Future Date Calculation using Set Analysis

sunny_talwar — Wed, 10 Aug 2016 16:43:47 GMT

Would you be able to share a sample. Not sure what might not be working

Re: Future Date Calculation using Set Analysis

cbaqir — Wed, 10 Aug 2016 17:05:01 GMT

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.

Re: Future Date Calculation using Set Analysis

sunny_talwar — Wed, 10 Aug 2016 17:42:01 GMT

What is the expected output for the table? with and without selection?

Re: Future Date Calculation using Set Analysis

cbaqir — Wed, 10 Aug 2016 17:45:49 GMT

The Lifetime Expressions 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 FY expressions should change based on Fiscal Year Selections. All FY numbers should be blank if one FY is not selected.

FY17:

FY Actuals: $0

FY Estimate to Complete: $40

FY Estimate At Completion: $40

FY Budget: $284

FY Variance to Budget: $244

FY16:

FY Actuals: $200

FY Estimate to Complete: $00

FY Estimate At Completion: $00

FY Budget: $284

FY Variance to Budget: $84

FY15:

FY Actuals: $325

FY Estimate to Complete: $00

FY Estimate At Completion: $325

FY Budget: $284

FY Variance to Budget: ($41)

FY14:

FY Actuals: $356

FY Estimate to Complete: $00

FY Estimate At Completion: $356

FY Budget: $284

FY Variance to Budget: ($72)

Re: Future Date Calculation using Set Analysis

sunny_talwar — Wed, 10 Aug 2016 18:25:03 GMT

I think one of your field name changed from FCST_AMT to fcST_AMT. Seems to be working now after the name is changed appropriately in the expressions.

Re: Future Date Calculation using Set Analysis

cbaqir — Wed, 10 Aug 2016 20:06:39 GMT

Ok, that issue aside, this calc needs to be specific for selected REQUEST_ID. The sample doesn't include this because I only pulled one REQUEST_ID.

Something like... 1-$ maybe?

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

Re: Future Date Calculation using Set Analysis

sunny_talwar — Wed, 10 Aug 2016 20:29:50 GMT