Dynamic Set Analysis

[richard_pearce6]

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

[#2]

If(FieldName = '' or null(),'*', sum(yourexpression))

[richard_pearce6]

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

[#4]

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))

[richard_pearce6]

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

[Oleg_Troyansky]

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

www.masterssummit.com