topic Re: Filtering within Set Analysis in QlikView

Filtering within Set Analysis

Mon, 28 Mar 2016 19:44:18 GMT

I'm trying to create a summary of data within a text box to display total planned spend where a project falls within 1 of 3 categories

Within Budget

Total Plan Spend

Variance from Budget

Over Budget

Total Plan Spend

Variance from Budget

Unbudgeted

Total Plan Spend

Variance from Budget

My basic formula for Total Plan Spend is:

sum({$<TYPE={'Plan'},FISCAL_YEAR={$(vFiscalYear)}>} cost)

How do I insert logic to filter this summary to only include projects within budget?

My formula for determining a project is within budget is:

sum({$<TYPE={'Plan'},FISCAL_YEAR={$(vFiscalYear)}>} cost)

divided by

sum({$<TYPE={'Budget'},FISCAL_YEAR={$(vFiscalYear)}>} cost)

is equal or less than 100%

swuehl — Mon, 28 Mar 2016 21:25:47 GMT

Maybe like

sum({$<

TYPE={'Plan'},

FISCAL_YEAR={$(vFiscalYear)},

Project = {"=sum({$<TYPE={'Plan'},FISCAL_YEAR={$(vFiscalYear)}>} cost) / sum({$<TYPE={'Budget'},FISCAL_YEAR={$(vFiscalYear)}>} cost) <=1"}

>} cost)

Mon, 28 Mar 2016 23:31:10 GMT

That solution worked perfectly. I tested it with one of the group summaries that I'm calculating and it worked without a hitch.

I've been working on this issue for a few days, and could not get it work as expected. Thank you, you're help is much appreciated!