Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
can we ignore a selection of field in set analysis having aggregate function
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 ?
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)))
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)))
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)
)
)
both are not giving desired output
just checked by removing interval .. the first suggestion works.. so how does this interval impacts the calculation
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.