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: 
polingolingo
Contributor
Contributor

Help with Street light gauge

 

Good afternoon,

I have been searching my question in the boards without luck.  Please forgive me if similar has been asked before.

I have sample data that looks like the below. I have four total deals and the deals are made up of different categories. the sum of all the categories make up my total margin.   In the example below, Deal1 made a total of -40. 

Q1 - What I want to create is a street light gauge with green and red lights & next to the lights, I would have the count of deals that either made money (green) or lost money (red). In the case of the example data below, I would have a gauge light that shows Green = 1 & Red = 3 (as three deals lost money & 1 made money) for a total of 4 deals.   

Q2.  Side question,  I want to have an object box that returns the total margin results on a specific category when a deal is selected. If I just want to see the "payables" total margin, and I select Deal1 - I would get the result of =40. But I do not want to have "payables" as a selected field. The only selected field would be deal 1.  

qlik.JPG 

Thank you in advance for your help and ideas 

 

Labels (1)
1 Solution

Accepted Solutions
13 Replies
rubenmarin

Hi, for Q1 you can use an expression like: If(Sum(TOTAL <Name> [total margin])<0, 0, 1)

and configure the lights based on that returned values.

 

For Q2 you can use set analysis to fix 'payables' as Category:

Sum({<Category={'payables'}>} [total margin])

 

 

polingolingo
Contributor
Contributor
Author

 

Good morning, 

Thank you for your reply.  for Q2 I am clear - exactly what I needed.

For Q1 - I was more looking for something like the below -  Where the Green & Red shows total distinct count depending on whether the deal made or lost money.   

count.PNGThank you again for your time 

rubenmarin

Hi, that's how the sample works, have you checked it? It'sa uploaded as attachment in the last post.
I configured it to show 0 as red and 1 as green, and the expression returns 0 (red) for deals with negative nargins and 1(green) for deals with positive margin
polingolingo
Contributor
Contributor
Author

 

I carefully reviewed your sample.  Obviously,my limited knowledge on gauges its becoming apparent. what I see in the sample is either a red and a green next to deal name -  But what I want is 1 traffic light that returns a total count.  How can I get there from what you've initially built? 

Thank you  again for your time on this. It helps me a lot. 

 

rubenmarin

Ok, my bad english and fast reading skipped the 'count' part, maybe with:

=RangeMax(0, RangeMin(1, Sum(Aggr(If(Sum(TOTAL <Name> [total margin])<0, -1, 1),Name))))

 

 

polingolingo
Contributor
Contributor
Author

 

Was the attachment meant to be the same as what you've sent previously? 

rubenmarin

No, it wasn't, I try again.

 

To show result 0 as green just add 1 to the expression:

=RangeMax(0, RangeMin(1, 1+Sum(Aggr(If(Sum(TOTAL <Name> [total margin])<0, -1, 1),Name))))

polingolingo
Contributor
Contributor
Author

I guess im getting more confused. sorry about this. 

 

Shouldnt the Red results = 3 (as 3 deals made <0 total margin). What you've built shows red = 0.

Ideally I have a light that shows Green = 1 & Red = 3. As below, 

count.PNG 

 

 

 

rubenmarin

That 0 or 1 is only to show the lights, to show the number you need another expression.
can you upload a sample of what chart are you using and you want to show this values?