Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
See attached file
Regards
Sorry, I have problems validating my license and for the time being use personal edition, so I can't open the file.
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
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
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)
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
Hi Jonathan, your suggestion worked flawlessly. I am very grateful for your answer.