topic Re: Straight table - sum total not working in QlikView

Straight table - sum total not working

Thu, 02 May 2013 11:27:28 GMT

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.

Re: Straight table - sum total not working

giakoum — Thu, 02 May 2013 11:33:58 GMT

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

Aggr(

, Client, [Bill Date])

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

sum(Aggr(

, Client, [Bill Date]))

Re: Straight table - sum total not working

Thu, 02 May 2013 11:46:22 GMT

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

Re: Straight table - sum total not working

Thu, 02 May 2013 11:46:46 GMT

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

Re: Straight table - sum total not working

giakoum — Thu, 02 May 2013 11:49:43 GMT

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

Re: Straight table - sum total not working

Thu, 02 May 2013 11:56:34 GMT

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]

Re: Straight table - sum total not working

giakoum — Thu, 02 May 2013 11:58:33 GMT

sample application would help if you cannot post the original

Re: Straight table - sum total not working

Thu, 02 May 2013 15:10:01 GMT

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.

Re: Straight table - sum total not working

Thu, 02 May 2013 16:05:38 GMT

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.

Re: Straight table - sum total not working

giakoum — Fri, 03 May 2013 07:53:08 GMT

is this the result you need ?

Re: Straight table - sum total not working

Fri, 03 May 2013 08:19:41 GMT

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?

Re: Straight table - sum total not working

giakoum — Fri, 03 May 2013 12:43:01 GMT

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.

Re: Straight table - sum total not working

Fri, 03 May 2013 13:41:44 GMT

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.

Re: Straight table - sum total not working

giakoum — Mon, 06 May 2013 12:21:03 GMT

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.

Re: Straight table - sum total not working

Tue, 07 May 2013 08:56:58 GMT

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