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

1 Solution

Accepted Solutions
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

View solution in original post

17 Replies
Anonymous
Not applicable
Author

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

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

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

Not applicable
Author

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

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

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

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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