Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I'm having some fun with some Dynamic Set Analysis. The calculation is basic and works as shown below:
CalcID = 1234 (the user will only select one CalcID at a time and the results are in a text box)
=sum( {<FieldA= {AAAA,BBBB}
,FieldB={A123,B123,C123}
,FieldC={'ZZZZ'}
,FieldD={999999}
,FieldE=P({1<LookUpField={'Look Up Value'}>} ReturnFields )
>} Sum_AMT)
The added level of complexity is Fields may not be used in all calculations. What drives the Fields I need to include and their conditions is a number of Meta tables with CalcID as the primary key.
The table will look something like this:
CalcID FieldName Value
1234 FieldA AAAA
1234 FieldA BBBB
1234 FieldB A123
1234 FieldB B123
Etc......
So first I need to know if FieldA exists
if it doesn't I either need to exclude it from the calculation or set it to "*"
or If the fields does exist I need to set the where clause
Can anyone help? I'm going down a dark route of nested variables and wondered if there's a cleaner solution to the problem?
Many thanks
Rich
If(FieldName = '' or null(),'*', sum(yourexpression))
Thanks Thomas,
Maybe I'm not understanding your response correctly
I tested conditional statements original expression so it would be something like this:
=sum( {< If(FieldA=True,FieldA= {AAAA,BBBB},FieldA=)
,FieldB={A123,B123,C123}
,FieldC={'ZZZZ'}
,FieldD={999999}
,FieldE=P({1<LookUpField={'Look Up Value'}>} ReturnFields )
>} Sum_AMT)
This is an example, I'd have to use conditional statements on all the fields in the final expression although QV doesn't seem to like conditional statements within Set Analysis
Rich
Well, im not sure i understanded your problem correctly
You need to check if FieldName is empty right? If it is empty FieldName should be * and value will be null() right?
I think i would do something like this:
=if(FieldName = null() or '', '*',
sum( {<FieldA= {AAAA,BBBB}
,FieldB={A123,B123,C123}
,FieldC={'ZZZZ'}
,FieldD={999999}
,FieldE=P({1<LookUpField={'Look Up Value'}>} ReturnFields )
>} Sum_AMT))
The issue I have is the conditional statements would be in the Set Analysis driven by the Meta table example in my first post. So the user will select CalcID, this would filter the Meta table to show the two Columns FieldName and FieldValue.
The expression would need to be dynamic to only perform Set Analysis on the fields in the Meta table.
Something like:
Sum({<
If('FieldA'= [True in Meta], FieldA = { [Corresponding Fields in Meta] } , FieldA = {"*"}
, If('FieldB'= [True in Meta], FieldB = { [Corresponding Fields in Meta] } , FieldB = {"*"}
, If('FieldC'= [True in Meta], FieldC = { [Corresponding Fields in Meta] } , FieldC = {"*"}
>} Sum_AMT))
Rich
Richard,
check out this blog article:
Q-Tip #6 - Those Tricky $-Sign Expansions | Natural Synergies
At the end of the article, I explain an example of how to do just that.
best,
Oleg Troyansky