Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
Brett_WP
New Contributor II

Set Analysis multiple criteria different sources

Hi all, new to Qlik  and need a little help please. 

I want to use a KPI box to display year-to-date Budget, without selecting any filters. 

How can I apply a formula to show Budget, only for month's where Actuals do not = $0    i.e. July - Apr contain actuals, May & June are $0. I want YTD budget excluding May & June.

 

Actuals =    TOT_AMT

Budget =    Cost (where Scenario = 'Bud')

Months =    FISC_MTH

I tried: sum({$<Scenario = {"Bud"}, TOT_AMT -= {0}>}Cost)

... but this is not returning the amount I expect. Close, but data is missing and I'm not sure why.

 

Thanks in advance.

1 Solution

Accepted Solutions
MVP
MVP

Re: Set Analysis multiple criteria different sources

Those future months are null, not 0. Depending on your data model, you could try:

sum({$<Scenario = {"Bud"}, TOT_AMT = {"*"}>} Cost)

This will select all values excluding nulls.

 

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
4 Replies
formosasol
New Contributor III

Re: Set Analysis multiple criteria different sources

Hi,
Can you supply example of the app
Thanks
Frank
Brett_WP
New Contributor II

Re: Set Analysis multiple criteria different sources

Thanks Frank, screen shot below:

As you can see May & June actuals are currently $0 as they are future months. I only want the Budget KPI box to show values up until April ($500K), ignoring the future months.

So whenever actuals = $0, ignore those budget months (being May & June in this example).

Note: Budget figures currently pull from an excel file which also contains Forecast numbers in the same column. Column name is "Scenario", filtering on "Bud" which excludes the forecast figures. i.e. Scenario = Bud

Hope that helps.

Example.PNG

 

MVP
MVP

Re: Set Analysis multiple criteria different sources

Those future months are null, not 0. Depending on your data model, you could try:

sum({$<Scenario = {"Bud"}, TOT_AMT = {"*"}>} Cost)

This will select all values excluding nulls.

 

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Brett_WP
New Contributor II

Re: Set Analysis multiple criteria different sources

This did the trick, thanks @jontydkpi 

Appreciate the quick response!