Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
sau953_Qlik
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
Master III
Master III

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
Master III
Master III

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.

sau953_Qlik
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.