topic How can I add dimension to expression in QlikView

How can I add dimension to expression

Thu, 26 May 2011 08:54:25 GMT

Hi,

I am trying to create counter for a number of projects that have spent value under the budget. I decided to use a Text Object for this.

When I do it as a Table Chart, then I can get the number by Summing up the result (3 in the example).

Project	Budget	Spent	Result (1= under, 0=over)
1	100	50	1
2	50	60	0
3	200	10	1
4	150	35	1
TOTAL	500	155	3

But how can I add it to one expression?

When I use if((Sum(Budget)-Sum(Spent))>0,1,0) in the table it works just fine, but in Text object QV will not know what budgets and spent go together.

Sum(Budget)-Sum(Spent) will give 345 and I cannot count the projects.

Thank you in advance!

Mariliis

How can I add dimension to expression

Thu, 26 May 2011 11:13:48 GMT

Hi Marilis,

you want in textbox for the above example the count 3 as answer? Do I understand you correctly?

How can I add dimension to expression

pat_agen — Thu, 26 May 2011 11:37:01 GMT

hi,

try this code

=sum(if(aggr(Sum(Budget-Spent),project)>0,1,0))

How can I add dimension to expression

Thu, 26 May 2011 11:57:29 GMT

Hi,

Yes, I'd like to use Text Object and answer for the example above should give 3.

How can I add dimension to expression

Thu, 26 May 2011 11:58:17 GMT

Hi,

This gives me 0, but thanks for answering, I'll look more into aggr function.

Re: How can I add dimension to expression

pat_agen — Thu, 26 May 2011 12:09:01 GMT

hi,

here is a qvw with the result I get. Maybe your dataset is different.

Re: How can I add dimension to expression

Thu, 26 May 2011 12:12:19 GMT

what Pat.agen wrote is correct.I wanted to write same too. I think the format might be different in your database.

How can I add dimension to expression

Thu, 26 May 2011 12:34:50 GMT

Hi,

I tried building the same way table and I get '-' for each row.

The data is coming from different tables that I have Concatenated together. PRKey is the same in both tables. Can this be the reason?

How can I add dimension to expression

Thu, 26 May 2011 12:41:13 GMT

what about this

Sum(if(aggr(Sum(TBBUSumTime)-Sum(TBIVSum),PRKey)<0,1,0))

How can I add dimension to expression

Thu, 26 May 2011 12:46:19 GMT

> had to be other way from the answer, but I got the same number now.

I did try adding Sum in front of both of them, but I added to many brackets.

Thank you both very much!

How can I add dimension to expression

pat_agen — Thu, 26 May 2011 12:46:40 GMT

hi,

yes it may well be especially if the budget and spent values are coming from the different load.

Try this variation which should get round this:

=sum(if(aggr(Sum(budget)-sum(spent),project)>0,1,0))

I didn't try this first time round as I didn't think it would work but have tried on my dataset and it works whereas the first solution I gave you does not.