Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
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.
This did the trick, thanks @jonathandienst
Appreciate the quick response!