Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Indicator graphic

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

BusinessProfitTraffic light gauge
Restaurant10,000green
Bar-3,000red
Hotel50,000green
Pub-7,000red
Supermarket100,000green

Min = -7,000red (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.

1 Solution

Accepted Solutions
MVP
MVP

Re: Indicator graphic

Hi

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
7 Replies
martinpohl
Valued Contributor II

Re: Indicator graphic

See attached file

Regards

Not applicable

Re: Indicator graphic

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

martinpohl
Valued Contributor II

Re: Indicator graphic

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

MVP
MVP

Re: Indicator graphic

Hi

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Indicator graphic

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)

MVP
MVP

Re: Indicator graphic

Hi

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Indicator graphic

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

Community Browser