Gauge Chart with different expressions based on dimension

[#1]

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

Senior Business Discovery Manager at Advectas AB

[Miguel_Angel_Baeyens]

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

[#3]

Hi Miguel,

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

@Ungvall

Senior Business Discovery Manager at Advectas AB

[Miguel_Angel_Baeyens]

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

[#5]

Hi,

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

@Ungvall

Senior Business Discovery Manager at Advectas AB

[Miguel_Angel_Baeyens]

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

[rbecher]

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

GetFieldSelections(DimensionField)

?

- Ralf

[#8]

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

Uploaded an example...

@Ungvall

Senior Business Discovery Manager at Advectas AB

[rbecher · Accepted solution]

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

- Ralf

[#10]

Thanks a million...

@Ungvall

Senior Business Discovery Manager at Advectas AB