Exclude sum results in straight table when 3 conditions are all met together

[Anonymous]

Hi All,

I am new to Qlik and have hit a roadblock so please can someone help me.

I am trying to exclude the sum results in a straight table when 3 conditions are all met together, each of them from different dimensions. I only want to do the exclusions within this straight table as i need the full data for other tables

My 3 Dimensions are Side, BusinessArea and TerminalNo and i want to sum the number of transactions from a field called NoOfTransactions.

I have 30 Sites all with unique names, 10 BusinessAreas all with unique names and  100's of Terminal numbers but not with unique names e.g terminal 1 will exist at all sites.

So what i need to do is exclude the results for only:

terminal 2 at Site10 for BusinessArea5

terminal 9 at Site21 for BusinessArea5

....etc ( i have approx 10 combinations in total)

I tried some set analysis myself and could exclude terminal 2 but it excluded terminal 2 everywhere, likewise with the site it excluded the sites i listed but excluded all terminals at those site.

Please help, thanks.

[#2]

You could create a flag in the script, so that if these three conditions are met, flag = 0, otherwise, flag = 1. Then in your expression use set analysis to exclude the fields with a 0 flag.

[Anonymous]

Hi Jacob,

Thanks for your response. I have lots of tables and already have lots of flags that apply to those so was hoping to avoid doing anything in the script just for this one table, as it's the only place it will ever apply.

I appreciate it may not be the best way but do you know if it can be done just within the table?

[#4]

You could use an if statement in each dimension:

if((condition1) or (condition2) or... or(condition10), NULL, Site)

if((condition1) or (condition2) or... or(condition10), NULL, BusinessArea)

if((condition1) or (condition2) or... or(condition10), NULL, TerminalNo)

[Anonymous]

Hi Jacob,

I see what you are saying but not sure how/what i enter for the conditions, can you really dumb it down for me e.g

if((Site=Site10) or (BusinessArea=BusinessArea5) or (Terminal=terminal 2), NULL, Site)

Thanks for your help on this.

[#6 · Accepted solution]

For example, if your two conditions were: terminal 2 at Site10 for BusinessArea5 and terminal 9 at Site21 for BusinessArea5

if(((TerminalNo = 2) and (Site = 'Site10') and (BusinessArea = 'BusinessArea5')) or ((TerminalNo = 9) and (Site = 'Site21') and (BusinessArea = 'BusinessArea5')), Null, Site)

if(((TerminalNo = 2) and (Site = 'Site10') and (BusinessArea = 'BusinessArea5')) or ((TerminalNo = 9) and (Site = 'Site21') and (BusinessArea = 'BusinessArea5')), Null, BusinessArea)

if(((TerminalNo = 2) and (Site = 'Site10') and (BusinessArea = 'BusinessArea5')) or ((TerminalNo = 9) and (Site = 'Site21') and (BusinessArea = 'BusinessArea5')), Null, TerminalNo)

So if you wanted more conditions you'd add another "or ((TerminalNo = x) and (Site = y) and (BusinessArea = z))"

[Anonymous]

Hi Jacob,

Thanks very much for explaining that and all your help, it worked perfectly.