topic Re: Help with Street light gauge in QlikView

Help with Street light gauge

polingolingo — Sat, 16 Nov 2024 21:39:12 GMT

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.

Thank you in advance for your help and ideas

Re: Help with Street light gauge

rubenmarin — Mon, 07 Jan 2019 10:06:55 GMT

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])

Re: Help with Street light gauge

polingolingo — Mon, 07 Jan 2019 15:30:30 GMT

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.

Thank you again for your time

Re: Help with Street light gauge

rubenmarin — Mon, 07 Jan 2019 16:34:39 GMT

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

Re: Help with Street light gauge

polingolingo — Mon, 07 Jan 2019 16:57:57 GMT

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

rubenmarin — Mon, 07 Jan 2019 17:14:56 GMT

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))))

Re: Help with Street light gauge

polingolingo — Mon, 07 Jan 2019 17:21:35 GMT

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

Re: Help with Street light gauge

rubenmarin — Mon, 07 Jan 2019 17:26:35 GMT

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))))

Re: Help with Street light gauge

polingolingo — Mon, 07 Jan 2019 17:32:23 GMT

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,

Re: Help with Street light gauge

rubenmarin — Mon, 07 Jan 2019 20:28:51 GMT

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?

Re: Help with Street light gauge

polingolingo — Mon, 07 Jan 2019 20:38:22 GMT

Ruben,

Maybe I was just being confusing with the traffic lights. What I am really looking for is a conditional count function based on that original straight table in which I get a "count" of results based on whether the deal made or lost money. So, what I need is a count function that returns = 3 for those deals that lost money and = 1 for the one deal that made money.

Thank you

Re: Help with Street light gauge

rubenmarin — Tue, 08 Jan 2019 08:45:46 GMT

Another try

Re: Help with Street light gauge

polingolingo — Tue, 08 Jan 2019 14:54:18 GMT

Perfect! thank you so much for all of your help Ruben.

Re: Help with Street light gauge

rubenmarin — Tue, 08 Jan 2019 17:14:24 GMT

Sorry for the confusing answers. If it's not clear what I have done:

- I used valuelist dimension to fix 2 rows.

- Check the expression for each light using pick(Match()) of the value list

- There is an expression that counts reds and other for greens (zero result is counted as green because the >=0)

- In presentation tab hide the dimension and table headers