Straight table - sum total not working

[#1]

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.

[#2]

Hi Ioannis,

From your last post, the table on the left is what my end result should be, so if you select EUR currency the total for client X should be £16.615, if you select GBP the amount is 15,000 etc.

Isuspecting my example is very basic, as in my actual model the amounts I have are in GBP like this example but I want the ability for the user to be able to slect any currency (EUR, HKD, USD, SGD, CHF) and it will take the indivdual lines and multiply by the specific rate giving a total for each client.

Hope that makes sense?

[giakoum · Accepted solution]

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.

[#4]

Thanks Ioannis,

I think this is gettign very close now 

If the EUR rate 1.14 date range is 01/11/10 to 15/11/10 and the second Euro rate is 1.28 for 16/11/10 to 30/11/10 - how can I get the sum of each of these rows give me an overall total for that client? As I think the max function will not work?

I really apprciate all your help so far with this.

[giakoum]

Interval match has taken care of that. if you have one rate for each interval, it doesn't matter if it is min or max or only. Max is only there if you have more that one rate for the same interval.

[#6]

  Thanks Ioannis,

Your help and patience with my query is really appreciated!

After looking at your example and identifying that I only have one rate per currency for each interval meant I just needed to tweak your formula to this, now it is working perfectly!

sum(TOTAL <[Client Group Name],[Client Name],[Matter Name], [BILLINGS Bill Date]>

Aggr(sum({$<CUR={$(=vSelCur)}>} [cdrate])*sum([BILLINGS FeesBilled Amt])

,[Client Group Name], [Client Name],[Matter Name], [BILLINGS Bill Date]))