topic Re: how to put a loop in my expression? in QlikView

how to put a loop in my expression?

Thu, 07 May 2015 13:56:34 GMT

Hello everybody,

I need your help for a bar chart where i need to see everything over 100%.

I have tried to check the box "relative" and it's close to work but the problem is:

If I select only one of my categories then this category is well over 100% (f.e.g. 56% True and 44% False)

But if I want to see all my categories then the total is over 100% but each one of the category is over 25%, 5% etc...

That's why I wanted to put a loop "foreach" like "foreach categories, do the expression" but it seems like I can't put a loop in an expression.

I'm not sure to be clear in my request. So I link to pictures: What I have and what I need

Re: how to put a loop in my expression?

Clever_Anjos — Thu, 07 May 2015 13:59:32 GMT

Would you mind putting your expression?

Let´s play with some SUM(total <category> values)

Re: how to put a loop in my expression?

Clever_Anjos — Thu, 07 May 2015 14:01:53 GMT

Please find attached an example

Re: how to put a loop in my expression?

Thu, 07 May 2015 14:16:31 GMT

I have a: count(values).

I just tried the SUM(total <category> values) and also count(total <category> values) but none of them is working..

And I can't open your attached example because I'm using a personal edition.

Re: how to put a loop in my expression?

Clever_Anjos — Thu, 07 May 2015 14:21:30 GMT

Try using

count(values). / count(total <category> values) and format your number as percent

Re: how to put a loop in my expression?

Thu, 07 May 2015 14:29:26 GMT

It almost work. But everything is reaching 12.5% like (CO2: 8.5% and 4.5% / Gas: 11% and 1.5% etc...)

How to make it reach 100%??

But thank you for the answer!

Re: how to put a loop in my expression?

Anonymous — Thu, 07 May 2015 14:34:02 GMT

try it.

Sum(fieldValue)/Sum(Total <Category> fielValue)

Sum(fieldValue)/Sum(Total fielValue)

and Properties--Tag Number Select

Regards!

Re: how to put a loop in my expression?

Thu, 07 May 2015 14:43:13 GMT

Still at the same point.

See what I get. I'm getting close but still not solved.

Re: how to put a loop in my expression?

Thu, 07 May 2015 14:50:11 GMT

I have 8 categories and that's why everything is over 12.5% and not 100%. I still have my "loop" problem. I need somewhere to put this foreach loop isnt it?

Re: how to put a loop in my expression?

Anonymous — Thu, 07 May 2015 14:50:39 GMT

you can share your app please

Re: how to put a loop in my expression?

Thu, 07 May 2015 14:57:08 GMT

Sorry but I can't share it.

Re: how to put a loop in my expression?

Thu, 07 May 2015 15:12:01 GMT

scramble your data if this is sensitive or try to create an example application to share, it is difficult to resolve without seeing what your underlying data model looks like

Joe

Re: how to put a loop in my expression?

vincent_ardiet — Thu, 07 May 2015 15:14:22 GMT

Hi Tristan,

It's strange because it should work.

I have build an example and it produces the expected result.

In the script I have a table like this:

LOAD * INLINE

[
Category, Status, Value
co2, 1, 45
coal, 1, 6
crude, 1, 89
formula, 1, 124
fuel, 1, 34
co2, 0, 8
coal, 0, 15
crude, 0, 10
formula, 0, 24
fuel, 0, 50
];

Then I have a chart with Category and Status in dimension and the following expression =sum(Value)/sum( total <Category> Value)

And I have choosed in the number format Integer and Show in Percent.

Re: how to put a loop in my expression?

Thu, 07 May 2015 15:30:03 GMT

True, your example is working, I tried it. Then I don't get why it's not working with my datas..

So thanks for helping, I ll try by my own now to solve it..

Thank all of you for your help!

Re: how to put a loop in my expression?

Thu, 07 May 2015 15:33:18 GMT

Well it's working finally !!!! I have to not to check the "relative" box! Thank You!