topic Gauge Chart with different expressions based on dimension in QlikView

Gauge Chart with different expressions based on dimension

Thu, 13 Oct 2011 16:06:58 GMT

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

Re: Gauge Chart with different expressions based on dimension

Miguel_Angel_Baeyens — Thu, 13 Oct 2011 16:18:46 GMT

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

Thu, 13 Oct 2011 16:27:14 GMT

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

Re: Gauge Chart with different expressions based on dimension

Miguel_Angel_Baeyens — Thu, 13 Oct 2011 16:39:50 GMT

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

Thu, 13 Oct 2011 16:43:57 GMT

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

Re: Gauge Chart with different expressions based on dimension

Miguel_Angel_Baeyens — Thu, 13 Oct 2011 17:08:31 GMT

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

rbecher — Thu, 13 Oct 2011 19:42:03 GMT

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

GetFieldSelections(DimensionField)

- Ralf

Gauge Chart with different expressions based on dimension

Fri, 14 Oct 2011 07:48:55 GMT

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

Uploaded an example...

@Ungvall

Senior Business Discovery Manager at Advectas AB

Re: Gauge Chart with different expressions based on dimension

rbecher — Fri, 14 Oct 2011 08:21:45 GMT

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

Fri, 14 Oct 2011 08:29:31 GMT

Thanks a million...

@Ungvall

Senior Business Discovery Manager at Advectas AB