Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

17 Replies
PrashantSangle

Hi,

Can you post sample data with expected output??

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
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

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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

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

Not applicable
Author

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

Not applicable
Author

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