Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Straight table - sum total not working

Hi,

I'm really hoping someone can help me. I cannot attach the file I'm using so hopefully the details are clear enough below - I'm so confused!!!

Within a table I have the following data:

Client            Bill Date          Amount

xxx               30/11/10           10,924

xxx               22/06/10           15.650

the Amount column works of the following expression:

sum({$<[BILLINGS Bill Date]={">=$(cddate1)<=$(cddate2)"},CUR={$(=vSelCur)}>}[BILLINGS FeesBilled Amt])

*

sum({$<[BILLINGS Bill Date]={">=$(cddate1)<=$(cddate2)"},CUR={$(=vSelCur)}>}cdrate)

which shows me a total of 26.574

However if I remove the bill date from the above table, I get a total of 53,054! (using the same expression as above).

Client             Amount

xxx                  10,924

I've looked on the website, and I suspect I need to use the aggr function within my expression, but I'm most probably wrong, why is the total not summing correctly and how can I get it to work?

Thanks.


Dave.

1 Solution

Accepted Solutions
giakoum
Honored Contributor II

Re: Straight table - sum total not working

Hi David.

since you are using interval match, there is no need to use set analysis for the limits. The only problem would be if you have overlapping dates in your rates table for example :

01/11/2010     15/11/2010     EUR     1.14

01/11/2010     30/11/2010     EUR     1.28

In this case, for billing date 10/11/2010, what is the corrrect rate? 1.14 or 1.28? I used max in my attached example.

Please check out the attached, but note :

1. I changed the variable expression

2. max is used for overlapping intervals

3. you need to get rid of the sync table

BR, Ioannis.

14 Replies
giakoum
Honored Contributor II

Re: Straight table - sum total not working

I presume you remove the field bill date from the chart? Then yes, you have to aggr :

Aggr(

     <your expression>

, Client, [Bill Date])

if this returns more than one value, you need to sum too :

sum(Aggr(

     <your expression>

, Client, [Bill Date]))

Not applicable

Re: Straight table - sum total not working

Sorry the second chart should show the total of both bill dates - so 53,054.

I've tried adding the below formula but it does not show anything.

aggr(sum({$<[BILLINGS Bill Date]={">=$(cddate1)<=$(cddate2)"},CUR={$(=vSelCur)}>}[BILLINGS FeesBilled Amt])

*

(sum({$<[BILLINGS Bill Date]={">=$(cddate1)<=$(cddate2)"},CUR={$(=vSelCur)}>}[cdrate]))

Not applicable

Re: Straight table - sum total not working

Sorry the second chart should show the total of both bill dates - so 53,054.

I've tried adding the below formula but it does not show anything.

aggr(sum({$<[BILLINGS Bill Date]={">=$(cddate1)<=$(cddate2)"},CUR={$(=vSelCur)}>}[BILLINGS FeesBilled Amt])

*

(sum({$<[BILLINGS Bill Date]={">=$(cddate1)<=$(cddate2)"},CUR={$(=vSelCur)}>}[cdrate]))

giakoum
Honored Contributor II

Re: Straight table - sum total not working

aggr(

sum({$<[BILLINGS Bill Date]={">=$(cddate1)<=$(cddate2)"},CUR={$(=vSelCur)}>}[BILLINGS FeesBilled Amt])

*

sum({$<[BILLINGS Bill Date]={">=$(cddate1)<=$(cddate2)"},CUR={$(=vSelCur)}>}cdrate)

, Client, [Bill Date])

but I am not sure what you mean by "Sorry the second chart should show the total of both bill dates - so 53,054."

Not applicable

Re: Straight table - sum total not working

What I mean is that the chart should only show the client name and the amount only.

I've added your formula to my table, but it still does not bring any value back:

aggr(

sum({$<[BILLINGS Bill Date]={">=$(cddate1)<=$(cddate2)"},CUR={$(=vSelCur)}>}[BILLINGS FeesBilled Amt])

*

sum({$<[BILLINGS Bill Date]={">=$(cddate1)<=$(cddate2)"},CUR={$(=vSelCur)}>}cdrate)

, [Client Name], [BILLINGS Bill Date]

giakoum
Honored Contributor II

Re: Straight table - sum total not working

sample application would help if you cannot post the original

Not applicable

Re: Straight table - sum total not working

I've mocked up an example of the application I'm using, note that the figures are in GBP, when you click on the table holding client, amount the figures should multiply by the rate in the image, so for HKD instead of showing 3,306 it shows 37,120.80! this is the problem I have my formula is not calculating correctly.

Not applicable

Re: Straight table - sum total not working

I have tried to hide these columns in my table but that doesn't seem to work as I get 2 lines still for the client, I have tried what Loannis has suggested as well but the formula always comes back as displaying nothing.

giakoum
Honored Contributor II

Re: Straight table - sum total not working

is this the result you need ?

Capture.PNG

Community Browser