Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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

How can I add dimension to expression

what about this

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

10 Replies
Not applicable

How can I add dimension to expression

Hi Marilis,

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

pat_agen
Valued Contributor

How can I add dimension to expression

hi,

try this code

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

Not applicable

How can I add dimension to expression

Hi,

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

Not applicable

How can I add dimension to expression

Hi,

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

pat_agen
Valued Contributor

Re: How can I add dimension to expression

hi,

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

Not applicable

Re: How can I add dimension to expression

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

How can I add dimension to expression

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

How can I add dimension to expression

what about this

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

Not applicable

How can I add dimension to expression

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

Community Browser