topic Re: Set analysis using if statement in QlikView

Set analysis using if statement

Anonymous — Wed, 24 Aug 2016 05:14:03 GMT

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?

Re: Set analysis using if statement

jonathandienst — Wed, 24 Aug 2016 05:21:14 GMT

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

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

Re: Set analysis using if statement

Anil_Babu_Samineni — Wed, 24 Aug 2016 05:23:08 GMT

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

Re: Set analysis using if statement

jonathandienst — Wed, 24 Aug 2016 05:23:17 GMT

To ignore the PERIOD NAME selection:

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

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

Re: Set analysis using if statement

Anonymous — Wed, 24 Aug 2016 05:37:53 GMT

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

Re: Set analysis using if statement

jonathandienst — Wed, 24 Aug 2016 05:41:09 GMT

Then it would look something like:

1 - Sum(CUSTOMER_IMPACT_MINS)/Sum(MINS_IN_MON)

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

Re: Set analysis using if statement

Anonymous — Wed, 24 Aug 2016 05:44:32 GMT

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

Re: Set analysis using if statement

Anonymous — Wed, 24 Aug 2016 05:55:29 GMT

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