Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Highlighted
polingolingo
New 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

Re: Help with Street light gauge

Another try

13 Replies

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
New Contributor

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 

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
New Contributor

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. 

 

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
New Contributor

Re: Help with Street light gauge

 

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

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
New Contributor

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 

 

 

 

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?
Community Browser