Set analysis using if statement

[Anonymous]

Hi..

I need to calculate service availability by using outage mins and total mins.

Fields are as below -

Total mins - MINS_IN_MON

Outage mins - CUSTOMER_IMPACT_MINS

Outage month - PERIOD NAME

I need to display YTD values only if Outage mins are greater than zero, and the formula needs to be independent of the filter Outage month i.e. PERIOD NAME

Formula for service availability is 1 - (CUSTOMER_IMPACT_MINS/MINS_IN_MON ).

Can somebody please help to write the expression?

[jonathandienst]

How would you like to aggregate the service availability over multiple months? I assume an average. Something like:

=Avg(Aggr(1 - (CUSTOMER_IMPACT_MINS/MINS_IN_MON), [PERIOD NAME]))

or

=Avg(Aggr(If(CUSTOMER_IMPACT_MINS > 0, 1 - (CUSTOMER_IMPACT_MINS/MINS_IN_MON)), [PERIOD NAME]))

[Anil_Babu_Samineni]

Can you try with any expression, if yes please post that

[jonathandienst]

To ignore the PERIOD NAME selection:

=Avg({<[PERIOD NAME]>} Aggr(1 - (CUSTOMER_IMPACT_MINS/MINS_IN_MON), [PERIOD NAME]))

or

=Avg({<[PERIOD NAME]>} Aggr(If(CUSTOMER_IMPACT_MINS > 0, 1 - (CUSTOMER_IMPACT_MINS/MINS_IN_MON)), [PERIOD NAME]))

[Anonymous]

Its not an average. Its merely sum of all the outage mins divided by the sum of total minutes

[jonathandienst · Accepted solution]

Then it would look something like:

1 - Sum(CUSTOMER_IMPACT_MINS)/Sum(MINS_IN_MON)

or

1 - Sum({<[PERIOD NAME]>} CUSTOMER_IMPACT_MINS)/Sum({<[PERIOD NAME]>} MINS_IN_MON)

[Anonymous]

the values get populated correctly but they change once I select the month filter. I need these to be independent of the filter

[Anonymous]

I made a few minor changes and this is how it worked

if(sum({<PERIOD_NAME=,}CUSTOMER_IMPACT_MINS)>0,1- (sum({<PERIOD_NAME=>}CUSTOMER_IMPACT_MINS)/sum({<PERIOD_NAME= >}MINS_IN_MON)))