Set analysis combined with if

[amber2000]

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

[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]

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]

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]

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 · Accepted solution]

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]

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