topic Re: Complicated sum - if or set analysis and syntax in QlikView

Complicated sum - if or set analysis and syntax

Tue, 17 Feb 2015 10:01:50 GMT

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

Anonymous — Tue, 17 Feb 2015 10:05:58 GMT

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

PrashantSangle — Tue, 17 Feb 2015 10:09:18 GMT

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

sunny_talwar — Tue, 17 Feb 2015 10:10:52 GMT

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,

Re: Complicated sum - if or set analysis and syntax

Tue, 17 Feb 2015 10:19:31 GMT

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

PrashantSangle — Tue, 17 Feb 2015 10:22:49 GMT

Hi,

Did you try my solution??

Regards

Re: Complicated sum - if or set analysis and syntax

sunny_talwar — Tue, 17 Feb 2015 10:25:37 GMT

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,

Re: Complicated sum - if or set analysis and syntax

Tue, 17 Feb 2015 10:46:35 GMT

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

Tue, 17 Feb 2015 10:50:11 GMT

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

Tue, 17 Feb 2015 10:57:52 GMT

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

PrashantSangle — Tue, 17 Feb 2015 11:03:13 GMT

Hi,

Can you post sample data with expected output??

Regards

Re: Complicated sum - if or set analysis and syntax

sunny_talwar — Tue, 17 Feb 2015 11:08:41 GMT

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,

Re: Complicated sum - if or set analysis and syntax

Tue, 17 Feb 2015 11:16:10 GMT

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

Tue, 17 Feb 2015 11:18:45 GMT

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

Tue, 17 Feb 2015 11:22:29 GMT

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

Re: Complicated sum - if or set analysis and syntax

sunny_talwar — Tue, 17 Feb 2015 11:29:16 GMT

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,

Re: Complicated sum - if or set analysis and syntax

Tue, 17 Feb 2015 11:44:08 GMT

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

Tue, 17 Feb 2015 11:47:33 GMT

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