set expression based on filter pane selection

[dreweezy]

Hello, I have a few filters in my dashboard and would like for my gauge chart to change depending on one specific filter pane I have called "ClientID". My ClientID filter pane only has 2 values which is CompanyA and CompanyB. 

The gauge has the expression:

if({<[ClientID] = {"CompanyA"}>} (Sum([Approved Budget]) / sum[Total Budget]))

For some reason I'm having trouble having this pass in qlik sense. Any thoughts? 

[Vegar · Accepted solution]

Try this:

Sum({<[ClientID] = {"CompanyA"}>} [Approved Budget]) / sum({<[ClientID] = {"CompanyA"}>} [Total Budget])

[dreweezy]

thank you this one does work, but how do I go about implementing another expression for CompanyB? Would an if statement suffice in this situation?

[Vegar]

I'm not really sure what you are asking for? Is ClientID associated with the rest of your data or is it just a data island table used for selecting layout stuff. If so then you could do like this

if([ClientID] = "CompanyA",
Sum([Approved Budget]) / sum([Total Budget]),
Sum(whatYouWant) / sum(Total what you want)
)

[dreweezy]

Since my filter pane has 2 values only - CompanyA and CompanyB how do I ensure in my expression that it will fetch the correct numbers when a user selects a value from the filter pane? CompanyA will populate given the syntax you just helped with. Now if a user selects CompanyB, how would I incorporate this into my expression? I tried the if statement you have provided but no luck on my end. I assume I don't need to explicitly tell qlik on the ,else statement that it is pertaining to CompanyB since I've already established CompanyA and all that is left is CompanyB. Thanks.

if([ClientID] = "CompanyA",
Sum([Approved Budget]) / sum([Total Budget]), 
Sum([Approved Budget]) / sum([Total Budget])
)

[Vegar]

Hos is your data model?
Could it be simplified like this?

Load * inline [
ClientID, Period,Approved Budget, Total Budget
CompanyA, 2019-04,1000,1200
CompanyB, 2019-04,2000,2200
CompanyA, 2019-03,1100,1300
]
Where
match(ClientID, 'CompanyA', 'CompanyB') > 0 //You only have CompanyA and CompanyB in your data?
;

Is it the dimension ClientID that is used in the filter pane?

[dreweezy]

I'd like to stay away from making any changes in the script.

This is what my data looks like.

Upon selecting either CompanyA and/or CompanyB on the filter pane I'd like for the gauge to change depending on the value selected in the ClientID filter pane. I am able to derive either CompanyA or CompanyB but once I add in the expression using an IF statement I am having some trouble producing/displaying any results. 

I need my syntax to say once someone selects CompanyA in the filter, give me the [Approved Budget] / [Total Budget] and else give me CompanyB [Approved Budget] / [Total Budget]. 

Hope this makes more sense.

if( ( [ClientID] = "CompanyA", [Approved Budget]/[Total Budget] ),

if ( [ClientID] = "CompanyB", [Approved Budget]/[Total Budget] )
)

[Vegar]

Your company A and B expressions are identical. Do you need to specify company at all in your expression?

The natural behavior of Qlik Sense is that selecting a value filters the expression with that selection.
When selecting a company will automatically give you the Sum([Approved Budget]) / sum([Total Budget]) for that company selection. If you have no selection or both companies selected you will get the Sum([Approved Budget]) / sum([Total Budget]) for both companies.

[dreweezy]

You're absolutely correct. I was definitely overthinking this and everything is working as expected from some preliminary data validation. Thank you all being patient with me.

[Vegar]

I've been there. Sometimes it is good tget get a set of fresh eyes to look at what you're doing.
-Vegar