Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Brett_WP
Contributor II
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.

Labels (2)
1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

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

View solution in original post

4 Replies
formosasol
Partner - Contributor III
Partner - Contributor III

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

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

 

jonathandienst
Partner - Champion III
Partner - Champion III

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
Contributor II
Contributor II
Author

This did the trick, thanks @jonathandienst 

Appreciate the quick response!