Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
what about this
Sum(if(aggr(Sum(TBBUSumTime)-Sum(TBIVSum),PRKey)<0,1,0))
Hi Marilis,
you want in textbox for the above example the count 3 as answer? Do I understand you correctly?
hi,
try this code
=sum(if(aggr(Sum(Budget-Spent),project)>0,1,0))
Hi,
Yes, I'd like to use Text Object and answer for the example above should give 3.
Hi,
This gives me 0, but thanks for answering, I'll look more into aggr function.
hi,
here is a qvw with the result I get. Maybe your dataset is different.
Hi
what Pat.agen wrote is correct.I wanted to write same too. I think the format might be different in your database.
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?
what about this
Sum(if(aggr(Sum(TBBUSumTime)-Sum(TBIVSum),PRKey)<0,1,0))
> 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!