topic Expression in table to achieve same result as select excluded on dimension in QlikView

Expression in table to achieve same result as select excluded on dimension

Anonymous — Fri, 17 Aug 2018 06:09:51 GMT

Hi all,

I have an expression in a table that gives me the availability of a system, ignoring the table dimension with TOTAL. This works as expected.

=exp(Sum(total log(aggr(((HourCount*3600) - Sum(Downtime))/(HourCount*3600),Block,Area))))

,'# ##0,000%')

My table dimension is [Description], and I'm trying to achieve with an expression what would be similar to the following:

Filter [Description] on a particular value, let's say "Timeout".
Select excluded on [Description] to exclude "Timeout" from the calculation, while keeping all other available values in the dimension.

The goal is then that each row in the table (that lists the different values of the given dimension) should calculate the total availability of the system, without the dimension value for the current row.

Does anyone have suggestions on how I can solve this?

I would love to add an example app, but I'm an end user creating my own tables in the brower through the QlikView plugin.

Best regards,

Knut

Re: Expression in table to achieve same result as select excluded on dimension

Anonymous — Fri, 17 Aug 2018 08:03:45 GMT

I have found a step in the right direction. If I do an excluding set analysis on the inner sum, I get the desired result, but this does obviously not work independently for each row, since the set analysis is evaluated for the whole chart.

=exp(

Sum(total

log(

aggr(

((HourCount*3600) - Sum( {$<Description -= {'Timeout'}} Downtime))/(HourCount*3600)

,Block,Area)

)

I can achieve the same result using an If statement, but I cannot get it to be dynamic for each row.

This works:

Sum(If(Description <> 'Timeout', Downtime))

This does not work:

Sum(If(Description <> Description , Downtime))

Is it possible to reference the dimension value for the current row in my chart?

Re: Expression in table to achieve same result as select excluded on dimension

Gysbert_Wassenaar — Fri, 17 Aug 2018 16:13:29 GMT

Perhaps like this:

=Sum({$<Description -= {'Timeout'}} total

log(

aggr(

(Only( {$<Description -= {'Timeout'}} HourCount)*3600

- Sum( $<Description -= {'Timeout'}}Downtime))

/ (Only( {$<Description -= {'Timeout'}} HourCount_*3600)

,Block,Area

)