Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How can I add dimension to expression

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

ProjectBudgetSpentResult (1= under, 0=over)
1100501
250600
3200101
4150351
TOTAL5001553

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

1 Solution

Accepted Solutions
Not applicable
Author

what about this

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

View solution in original post

10 Replies
Not applicable
Author

Hi Marilis,

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

pat_agen
Specialist
Specialist

hi,

try this code

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

Not applicable
Author

Hi,

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

Not applicable
Author

Hi,

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

pat_agen
Specialist
Specialist

hi,

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

Not applicable
Author

Hi

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

Not applicable
Author

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?

Screen shot 2011-05-26 at 3.28.33 PM.png

Not applicable
Author

what about this

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

Not applicable
Author

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