Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
jonathandienst
Partner - Champion III
Partner - Champion III

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

View solution in original post

7 Replies
martinpohl
Partner - Master
Partner - Master

See attached file

Regards

Not applicable
Author

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

martinpohl
Partner - Master
Partner - Master

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

jonathandienst
Partner - Champion III
Partner - Champion III

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
Author

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)

jonathandienst
Partner - Champion III
Partner - Champion III

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
Author

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