Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
saurabh_Qlik1
Contributor III
Contributor III

Apply alternate state without aggregation in Set Expression

Hi,

I have a two sheet app.

On sheet 1 (default state) I have a filter with dates. On sheet 2 (Alt state 1) I have a chart with with a filter with 100 companies to choose from. I want to be able to choose a company from my sheet 2 filter but at the same time pick up the date selected on sheet 1 from the default state. 

 

My expression before applying alternate state is as follows:

Sum([Amount Outstanding Funded] * FX * [Val Date Discount]) / [Total EUR Outstanding]

 

I am able to apply the alternate state to the first part of my expression:

Sum({[Alt State 1]<ValuationDate = $::ValuationDate>}([Amount Outstanding Funded] * FX * [Val Date Discount])) / [Total EUR Outstanding]

However, this does not give me the correct answer since the part of the expression after the "/" sign does not pick up the alternate state rule.

How I do I achieve this?

Thanks!

Labels (3)
1 Solution

Accepted Solutions
Or
MVP
MVP

1) Set analysis requires an aggregation function.

2) In an expression, if you use  a field without including an aggregation function, that is equivalent to using Only(FieldName)

So, if this is a reference to a field, you could use Only( {[Alt State 1]<ValuationDate = $::ValuationDate>} [Total EUR Outstanding]) instead of just the field name and get the same result.

If this is instead a reference to a master measure, you will have to edit the formula of the master measure to include the set analysis.

View solution in original post

3 Replies
vinieme12
Champion III
Champion III

Sum({[Alt State 1]<ValuationDate = $::ValuationDate>}([Amount Outstanding Funded] * FX * [Val Date Discount])) / Sum({[Alt State 1]}[Total EUR Outstanding])

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Or
MVP
MVP

1) Set analysis requires an aggregation function.

2) In an expression, if you use  a field without including an aggregation function, that is equivalent to using Only(FieldName)

So, if this is a reference to a field, you could use Only( {[Alt State 1]<ValuationDate = $::ValuationDate>} [Total EUR Outstanding]) instead of just the field name and get the same result.

If this is instead a reference to a master measure, you will have to edit the formula of the master measure to include the set analysis.

saurabh_Qlik1
Contributor III
Contributor III
Author

Thank you so much for your replies. I kept on trying to use Only() while not realizing I was applying it to a master measure.  Edited the formula in the master measure and its working fine.