topic Re: Indicator graphic in QlikView

Indicator graphic

Tue, 31 Jul 2012 10:25:10 GMT

Hi, I've created the following table with a red traffic light if the profit is negative:

Business	Profit	Traffic light gauge
Restaurant	10,000	green
Bar	-3,000	red
Hotel	50,000	green
Pub	-7,000	red
Supermarket	100,000	green
	Min = -7,000	red (since minimum is -7,000, which is negative)

As you can see, on the last row I have the minimum profit and a red traffic light gauge if at least one of the businesses have negative profit.

I cloned this table and I've been trying to create an indicator graphic (with a traffic light) that is red if minimum of those busenesses has negative profit (which means at least one business has a negative profit).

I can't use min(Profit) since profit isn't a dimension, but an expression that can only be calculated if the table has the dimension business.

Re: Indicator graphic

martinpohl — Tue, 31 Jul 2012 10:50:07 GMT

See attached file

Regards

Re: Indicator graphic

Tue, 31 Jul 2012 11:40:31 GMT

Sorry, I have problems validating my license and for the time being use personal edition, so I can't open the file.

Re: Indicator graphic

martinpohl — Tue, 31 Jul 2012 11:43:33 GMT

Crate a straight table chart

Choose the dimension Business

Create formula 1 with syntax

if(dimensionality() =1,

sum(Profit),

min(aggr(sum(Profit),Business)))

create formula 2, same syntax.

Choose the visible state from text to traffic light and set the limits

Re: Indicator graphic

jonathandienst — Tue, 31 Jul 2012 11:55:05 GMT

Assuming that table has Business as its dimension, and the chart epression is Sum(Profit), change the expression to:

=Min(Aggr(Sum(Profit), Business))

This should work correctly for the normal rows and will return the minimum value for the total row.

Regards

Jonathan

Re: Indicator graphic

Tue, 31 Jul 2012 12:07:59 GMT

Jonathan and Martin, thank you for your answers, but I'm affraid my Profit expression is a little complicated and doesn't work with your suggestions.

The expression is something like this:

=if(Credit_Limit>0 ,sum(Pendent)-sum(if((RegisteredYear='2012' and RegisteredMonth=$(Vmonth) ),Pendent,0))-sum(if((RegisteredYear='2012' and RegisteredMonth=$(Vmonth)-1 ),Pendent,0))-sum(if((RegisteredYear='2012' and RegisteredMonth=$(Vmonth)-2 ),Pendent,0))-Credit_Limit, 0)

Re: Indicator graphic

jonathandienst — Tue, 31 Jul 2012 12:17:08 GMT

Then the following should work:

Min(Aggr(

If(Credit_Limit>0,

sum(Pendent)

-sum(if((RegisteredYear='2012'and RegisteredMonth=$(Vmonth) ), Pendent,0))

-sum(if((RegisteredYear='2012' and RegisteredMonth=$(Vmonth)-1 ),Pendent,0))

-sum(if((RegisteredYear='2012' and RegisteredMonth=$(Vmonth)-2 ),Pendent,0))-Credit_Limit, 0),

Business))

If there are other dimensions, then replace "Business" with the list of dimensions:

Min(Aggr(........, dim1, dim2, dim3))

Regards

Jonathan

Re: Indicator graphic

Wed, 01 Aug 2012 07:57:59 GMT

Hi Jonathan, your suggestion worked flawlessly. I am very grateful for your answer.