Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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])
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])
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'.
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])
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
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])
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
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])
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
* 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.