can we ignore a selection of field in set analysis having aggregate function

[#1]

can we ignore a selection of field in set analysis having aggregate function

[JonnyPoole]

Might need some more help on your question.

If you have an expression like the following:

Sum(Sales)

You can use Set Analysis to have it explicitly ignore a field selection (say Customer)

Sum( {<Customer]=>} Sales)

You can also use aggr() in a set analyis modifier to ignore customers with only one Contact ?

Sum( {<Customer={"=aggr(count(distinct Contact),Customer)>1"}>}  Sales) 

Does that help ?

[#3]

i don't want the list box selection to impact the values of the below expression which is working fine without selection of Process field

=Sum({$<[outage_type]={1},gc_rec_ID={692},Link_Flag={'Spectrum'}>}  Aggr(1- Num((Sum({$<[outage_type]={1},gc_rec_ID={692},Link_Flag={'Spectrum'}>} duration_sec)/Num#(Interval(MonthEnd([Call_Year-Month])-MonthStart([Call_Year-Month]),'ss'))),'####.#####'),model_key)))

[settu_periasamy]

Hi,

Instead of $, try to give {1}

May be like this..

=Sum({{1}<[outage_type]={1},gc_rec_ID={692},Link_Flag={'Spectrum'}>}  Aggr(1- Num((Sum({{1}<[outage_type]={1},gc_rec_ID={692},Link_Flag={'Spectrum'}>} duration_sec)/Num#(Interval(MonthEnd([Call_Year-Month])-MonthStart([Call_Year-Month]),'ss'))),'####.#####'),model_key)))

[JonnyPoole]

You can add Process= in each set analysis statement (inside and outside the aggr() )  to eliminate the selection at each level of aggregation.

What bothers me though is that the denominator where you calculate an interval has no aggregation function , and it should.  Is this because there is only 1 possible month in the selection ?  if that is the case , you may need to wrap the 2 fields in only() functions and apply the set analysis there as well

Either try this:

=

Sum({$<Process=,[outage_type]={1},gc_rec_ID={692},Link_Flag={'Spectrum'}>} 

Aggr(

     1

     -

     Num(

          (

               Sum({$<Process=,[outage_type]={1},gc_rec_ID={692},Link_Flag={'Spectrum'}>} duration_sec)

               /

               Num#(Interval(MonthEnd([Call_Year-Month])-MonthStart([Call_Year-Month]),'ss'))

          )

     ,'####.#####')

,model_key)

)

)

or this :

=

Sum({$<Process=,[outage_type]={1},gc_rec_ID={692},Link_Flag={'Spectrum'}>}

Aggr(

     1

     -

     Num(

          (

               Sum({$<Process=,[outage_type]={1},gc_rec_ID={692},Link_Flag={'Spectrum'}>} duration_sec)

               /

               Num#(Interval(MonthEnd( only({<Process]=>} [Call_Year-Month] ) )-MonthStart(only({<Process]=>} [Call_Year-Month] ) ),'ss'))

          )

     ,'####.#####')

,model_key)

)

)

[#6]

both are not giving desired output

[#7]

just checked by removing interval .. the first suggestion works.. so how does this interval impacts the calculation

[JonnyPoole]

The interval is being calculated based on the selection state of [Call_Year-Month].

When you select a Process, the number of Call_Year-Month values in the selection state can change,  so you need to prevent that .  Using Set Analysis you can do that , but you must have an aggregation function on the field to do that  (only(), sum(), count() etc...) .

I can only guess what may be a proper aggregation function to employ here without your data model. If you can share i might have some more ideas.