Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Filtering within Set Analysis

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%

1 Solution

Accepted Solutions
MVP
MVP

Re: Filtering within Set Analysis

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)


2 Replies
MVP
MVP

Re: Filtering within Set Analysis

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)


Not applicable

Re: Filtering within Set Analysis

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!

Community Browser