Complicated sum - if or set analysis and syntax

[#1]

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

[Anonymous]

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

[PrashantSangle]

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

[sunny_talwar]

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

[#5]

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

[PrashantSangle]

Hi,

Did you try my solution??

Regards

[sunny_talwar]

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

[#8]

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

[#9]

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

[#10]

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