Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Highlighted
polingolingo
Not applicable

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
rubenmarin
Not applicable

Re: Help with Street light gauge

Another try

13 Replies
rubenmarin
Not applicable

Re: Help with Street light gauge

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
Not applicable

Re: Help with Street light gauge

 

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
Not applicable

Re: Help with Street light gauge

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
Not applicable

Re: Help with Street light gauge

 

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
Not applicable

Re: Help with Street light gauge

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
Not applicable

Re: Help with Street light gauge

 

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

rubenmarin
Not applicable

Re: Help with Street light gauge

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
Not applicable

Re: Help with Street light gauge

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
Not applicable

Re: Help with Street light gauge

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?