17 Replies Latest reply: Feb 17, 2015 6:49 AM by Andre Toerien

# Complicated sum - if or set analysis and syntax

Hi

I have the following sum I need to do on a chart. What would be the best way to do this. My gut feel says set analysis but I can't see an example of something like this on the forums

This is it in SQL basically:

If TransType not in (6,7) and TransType = 8 and TransSubType in(1,2,3,4) sum  either KmUnit or Hours depending on which has a value. Only one of the two will have a value

Else  If TransType not in (6,7) sum Quantity

So TransType 6 and 7 always need to be ignored in the sums - this is where I think set analysis is the way to go.

The basic sums is not hard doing but the combination is an issue, maybe I am over complicating it. Any advice especially with the syntax would be great

• ###### Re: Complicated sum - if or set analysis and syntax

I would suggest to prepare your requirements in the script.

provide a flag which you can use in set Analysis!

Advantage is that you donot need a complex set analysis

and depending onthe amount of data it will be fater

• ###### Re: Complicated sum - if or set analysis and syntax

This may be a good option, I am a bit pressed for time though.

If I understand you correctly I would for example have a field with a 1 for Type 8 subtype 1,2,3,4, 2 would be all other tran type except 6 and 7?

The issue in the script still remains the same as I said to the other guys, how do I handle the else, adding in the record with value 2. Tis I think is my main issue to resolve at this stage,

Other than that I really also like your way of thinking, I just don't see it as a full solution

• ###### Re: Complicated sum - if or set analysis and syntax

Hi,

Try

if(not isnull(KmUnit),

sum({<TransType={'8'},TransSubType={'1','2','3','4'}>} KmUnit),

sum({<TransType={'8'},TransSubType={'1','2','3','4'}>} Hours)

)

Regards

• ###### Re: Complicated sum - if or set analysis and syntax

I can't think of a set analysis statement, but why not use a nested if statement? Try if this works:

If(TransType = 8 and Match(TransSubType, 1, 2, 3, 4), Sum(If(IsNull(KmUnits), Hours, KmUnit)), Sum(Quantity)

I think the more efficient way would be to flag these number in the script and then do a sum using set analysis.

HTH

Best,

S

• ###### Re: Complicated sum - if or set analysis and syntax

sunindia wrote:

I can't think of a set analysis statement, but why not use a nested if statement? Try if this works:

If(TransType = 8 and Match(TransSubType, 1, 2, 3, 4), Sum(If(IsNull(KmUnits), Hours, KmUnit)), Sum(Quantity)

I think the more efficient way would be to flag these number in the script and then do a sum using set analysis.

HTH

Best,

S

I like your answer best, how do I exclude TransType 6 and 7 from the sum(Quantity)? It would be a nested if probably

• ###### Re: Complicated sum - if or set analysis and syntax

Hi,

Did you try my solution??

Regards

• ###### Re: Complicated sum - if or set analysis and syntax

Not yet, going to try it but it also excludes the else statement where it needs to sum(Quantity)  if the TransType <> 6,7

So the first one sums TransType 8 subtype 1234, the second option sums everything else except for TransType 6 and 7

• ###### Re: Complicated sum - if or set analysis and syntax

Hi,

Can you post sample data with expected output??

Regards

• ###### Re: Complicated sum - if or set analysis and syntax

Attached. I used some shortening of field names, the actual field names are in the file

• ###### Re: Complicated sum - if or set analysis and syntax

The results is a bit more difficult to give you, on our full set of data I know what the sum should be

• ###### Re: Complicated sum - if or set analysis and syntax

Check if this make sense? PFA

I did it in the script by creating flags and I wasn't sure if you are trying to create a chart or text object. I have created a text object, because there is no dimension to display this information as a straight table.

Best,

S

• ###### Re: Complicated sum - if or set analysis and syntax

Makes sense yes, I added the flags to the records, the flagging is correct but it it still summing tran types 6 and 7, going to look at making 6 and 7 flag 3, not sure if it will help

• ###### Re: Complicated sum - if or set analysis and syntax

Tried flag 3, still adding up tran types 6 and 7, no idea why the code looks 100%, had the same issue with another set analysis I got from the community

Sorry, looked at the wrong column, I have an Amount column which is still my old code which is wrong, this sum in the example you sent is working 100%

Thanks a ,million

• ###### Re: Complicated sum - if or set analysis and syntax

Update: I think I don't understand the second part of your requirement? Else If TransType not in (6,7) sum Quantity? Can you explain that a little?

Does TransType take values other than 6,7,8?

HTH

Best,

S

• ###### Re: Complicated sum - if or set analysis and syntax

Yes, For these charts I have to ignore values in the sum for TransType 6 and 7, the first part of the if statement sorts this as it is hardcoded so to speak to TransType 8

In the else then everything else needs to be summed except if the TransTyoe is 6 or 7

• ###### Re: Complicated sum - if or set analysis and syntax

Got it, try if this works

If(TransType = 8 and Match(TransSubType, 1, 2, 3, 4), Sum(If(IsNull(KmUnits), Hours, KmUnit)), If(not Match(TransType, 6, 7), Sum(Quantity))

I have not tested this, so you might have to play around with this a little.

Let me know if you need more help.

Best,

S

• ###### Re: Complicated sum - if or set analysis and syntax

Something is wrong with the syntax, says garbage after expression if, am looking if I can find it

Something is wrong with the syntax, says garbage after expression ",", am looking if I can find it