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

[PrashantSangle]

Hi,

Can you post sample data with expected output??

Regards

[sunny_talwar]

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

[#4]

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

[#5]

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

[#6]

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

[sunny_talwar · Accepted solution]

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

[#8]

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

[#9]

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