9 Replies Latest reply: Oct 14, 2011 4:29 AM by Torbjörn Ungvall

Gauge Chart with different expressions based on dimension

Hi,

I've a Gauge Chart that shows, for example operating costs in %:

-(-(sum( {\$<RR1_ReportRow={'Summa Driftskostnader'}>} RR_Belopp ) /
sum( {\$<RR1_ReportRow={'Summa premieintäkt'}>} RR_Belopp ))

But if customer choose a dimension I have to use another expression:

-(sum( {\$<RR_Affär={'Försäkring'}, RR2_ReportRow={'Summa Driftskostnader'}>} RR_Belopp ) /
sum( {\$<RR_Affär={'Försäkring'}, RR2_ReportRow={'Summa premieintäkt'}>} RR_Belopp ))
-
(sum( {\$<RR_Affär={'Försäkring'}, RR2_ReportRow={'Ersättning Agria'}>} RR_Belopp ) /
sum( {\$<RR_Affär={'Försäkring'}, RR2_ReportRow={'Summa premieintäkt'}>} RR_Belopp ))

In a straight table I can use Dimensionality()=0, but that won't work here. Is there another way to do this without partial sums?

@Ungvall

• Re: Gauge Chart with different expressions based on dimension

Hi Ungvall,

How many dimensions (not values) can the user select from? Would it do using a conditional like?

```=If(GetFieldSelections(DimensionField) = 0,
-(-(sum( {\$<RR1_ReportRow={'Summa Driftskostnader'}>} RR_Belopp ) /
sum( {\$<RR1_ReportRow={'Summa premieintäkt'}>} RR_Belopp ))
,
-(sum( {\$<RR_Affär={'Försäkring'}, RR2_ReportRow={'Summa Driftskostnader'}>} RR_Belopp ) /
sum( {\$<RR_Affär={'Försäkring'}, RR2_ReportRow={'Summa premieintäkt'}>} RR_Belopp ))
-
(sum( {\$<RR_Affär={'Försäkring'}, RR2_ReportRow={'Ersättning Agria'}>} RR_Belopp ) /
sum( {\$<RR_Affär={'Försäkring'}, RR2_ReportRow={'Summa premieintäkt'}>} RR_Belopp ))
)
```

If so, then you can use a variable or a Concat() within set analysis so RR_Affär will always have the same values the user has selected (or even removing that) so when no selections are made, the total shows, otherwise the selection affects the result.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

• Gauge Chart with different expressions based on dimension

Hi Miguel,

The user can choose from 4 different dimension, like Segment and Market... Shall I swop DimensionField above with my different dimensions?

@Ungvall

• Re: Gauge Chart with different expressions based on dimension

Hi,

You can use something like this instead

```WildMatch(GetCurrentSelections(Chr(39) & Chr(44) & Chr(39), ':'), '*Dimension1*', '*Dimension2*')
```

Not very clean, though...

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

• Gauge Chart with different expressions based on dimension

Hi,

Is Dimension1* and Dimension2* my dimensions or the expressions. If dimensions, where will the different expressions come in?

@Ungvall

• Re: Gauge Chart with different expressions based on dimension

Hi,

Those are dimensions (field names) to be used instead of the GetFieldSelections() suggested above. Using the same example I used, the resulting expression for your chart (it's a gauge chart, so there are no dimensions) would be

```=If(WildMatch(GetCurrentSelections(Chr(39) & Chr(44) & Chr(39), ':'), '*Dimension1*', '*Dimension2*') = 0,
-(-(sum( {\$<RR1_ReportRow={'Summa Driftskostnader'}>} RR_Belopp ) /
sum( {\$<RR1_ReportRow={'Summa premieintäkt'}>} RR_Belopp ))
,
-(sum( {\$<RR_Affär={'Försäkring'}, RR2_ReportRow={'Summa Driftskostnader'}>} RR_Belopp ) /
sum( {\$<RR_Affär={'Försäkring'}, RR2_ReportRow={'Summa premieintäkt'}>} RR_Belopp ))
-
(sum( {\$<RR_Affär={'Försäkring'}, RR2_ReportRow={'Ersättning Agria'}>} RR_Belopp ) /
sum( {\$<RR_Affär={'Försäkring'}, RR2_ReportRow={'Summa premieintäkt'}>} RR_Belopp ))
)
```

So if in the current state of selections Dimension1 or Dimension2 (as many as you need) have selections, WildMatch() will return greater than zero, then entering the "else" part of the conditional, showing the particular expression. If there are no selections, the "then" part of the conditional will be evaluated, showing the total.

Hope that makes sense.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

• Gauge Chart with different expressions based on dimension

Why not use two different gauge charts with an opposite conditional show on

`GetFieldSelections(DimensionField)`

?

- Ralf

• Gauge Chart with different expressions based on dimension

Unfortunately ther's a lot og Gauge Charts...

@Ungvall

• Re: Gauge Chart with different expressions based on dimension

See attached example. Although I don't know if the expression is right.. You can also encapsulate the condition in a variable.

- Ralf

• Gauge Chart with different expressions based on dimension

Thanks a million...

@Ungvall