Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
amber2000
Creator
Creator

Set analysis combined with if

Hello everyone,

I have a set analysis and I want to combine it with an if.

After reading a few discussions on this subject I understand that this is not the way to go (we work with big datasets)

This is my Set Analysis expression:

=Sum({$ <[SD_WH] -= {12}, [Aisle_Zone] -= {PBS_Containers, PBS_Displays}> }[SD_Shipped_Colli])

Now I also want to Exclude values from the sum based on this if statement:

If (Article_FlowType = 'PBS' and (Article_TI * Article_HI = 1 ))

I'm not able to include this in the above expression because I also have an Article_FlowType named 'PBL' with a TI * HI = 1 that needs to be included in the sum.

Can anyone help me with this please? This is above my knowledge.

Kind regards,

Monique

1 Solution

Accepted Solutions
sunny_talwar

What if you add >= in there:

=Sum({$ <[SD_WH] -= {12}, [Aisle_Zone] -= {PBS_Containers, PBS_Displays}> *<[SD_Shipped_Colli] = {"=Article_TI * Article_HI >= 1"}, Article_FlowType -= {'PBS'}>}[SD_Shipped_Colli])

View solution in original post

11 Replies
sunny_talwar

May be try this:

=Sum({$<[SD_WH] -= {12}, [Aisle_Zone] -= {PBS_Containers, PBS_Displays}, Article_FlowType = {'PBS'}, [SD_Shipped_Colli] = {"=Article_TI * Article_HI = 1"}>} [SD_Shipped_Colli])

amber2000
Creator
Creator
Author

If I do that then Article_FlowType 'PBL' is exluded, I only want to exclude the values 1 (based on Article_TI * Article_HI) when Article_FlowType = 'PBS' and not when Article_FlowType = 'PBL'.

sunny_talwar

I see you want to exclude this and not include. Can you try this:

=Sum({$<[SD_WH] -= {12}, [Aisle_Zone] -= {PBS_Containers, PBS_Displays}, Article_FlowType -= {'PBS'}, [SD_Shipped_Colli] -= {"=Article_TI * Article_HI = 1"}>} [SD_Shipped_Colli])

amber2000
Creator
Creator
Author

Sorry but this has the same result, PBL is excluded.

I will try to explain my requirements in a simpler way:

When  Article_TI * Article_HI = 1 from Article_FlowType 'PBS' do not include this records in the Sum of SD_Shipped_Colli

When Article_TI * Article_HI = 1 from Article_FlowType 'PBL' include this records in the Sum of SD_Shipped_Colli

The result of the above must then be integrated in the expression:

=Sum({$ <[SD_WH] -= {12}, [Aisle_Zone] -= {PBS_Containers, PBS_Displays}> }[SD_Shipped_Colli])


Monique

sunny_talwar

Not sure how your first two condition relate to the new ones.

Is it or between them (include stuff from 1st conditions -> [SD_WH] -= {12}, [Aisle_Zone] -= {PBS_Containers, PBS_Displays} or from the 2nd condition -> Article_TI * Article_HI = 1 from Article_FlowType 'PBS')

Is it and betweem them (include stuff from 1st conditions -> [SD_WH] -= {12}, [Aisle_Zone] -= {PBS_Containers, PBS_Displays} and from the 2nd condition -> Article_TI * Article_HI = 1 from Article_FlowType 'PBS')


If it is or, try this:

=Sum({$ <[SD_WH] -= {12}, [Aisle_Zone] -= {PBS_Containers, PBS_Displays}> + <[SD_Shipped_Colli] = {"=Article_TI * Article_HI = 1"}, Article_FlowType -= {'PBS'}>}[SD_Shipped_Colli])


If it is and, try this:

=Sum({$ <[SD_WH] -= {12}, [Aisle_Zone] -= {PBS_Containers, PBS_Displays}> * <[SD_Shipped_Colli] = {"=Article_TI * Article_HI = 1"}, Article_FlowType -= {'PBS'}>}[SD_Shipped_Colli])

amber2000
Creator
Creator
Author

Hi Sunny,

I made an qvw example with some test data.

However It’s impossible to attach my example and therefor I’m sending it by mail.

In the Main tab you'll see 3 pivot tables:

1) is my original code without the If I want to include

2) is your code for the OR and is not working for what I want

3) is your code for the AND and it is working when Article_TI * Article_HI = 1 but not when Article_TI * Article_HI > 1

In the tab Detail is the table with the testdata

Kind regards,

Monique

sunny_talwar

What if you add >= in there:

=Sum({$ <[SD_WH] -= {12}, [Aisle_Zone] -= {PBS_Containers, PBS_Displays}> *<[SD_Shipped_Colli] = {"=Article_TI * Article_HI >= 1"}, Article_FlowType -= {'PBS'}>}[SD_Shipped_Colli])

amber2000
Creator
Creator
Author

Hi Sunny,

Now I see it working, thanks a million.

I have one more question:

How does the additional set modifier works? because it's the first time I see this kind of

......... * <[SD_Shipped_Colli] = {"=Article_TI * Article_HI >= 1"}, Article_FlowType -= {'PBS'}>


To me it looks like a nested set but what is the function of the * between the two sets?


Kind regards,

Monique

sunny_talwar

* is telling the expression to get intersection of the two sets. + is used for union of the two sets. Find attached a set analysis guide which I thought was very helpful when I was learning all this stuff.

If you got what you were looking for, I would request you to close this thread by selecting correct and helpful answers.

Qlik Community Tip: Marking Replies as Correct or Helpful