Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted

Re: Set analysis combined with if

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
Highlighted

Re: Set analysis combined with if

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

Highlighted
Creator
Creator

Re: Set analysis combined with if

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'.

Highlighted

Re: Set analysis combined with if

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

Highlighted
Creator
Creator

Re: Set analysis combined with if

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

Highlighted

Re: Set analysis combined with if

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

Highlighted
Creator
Creator

Re: Set analysis combined with if

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

Highlighted

Re: Set analysis combined with if

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

Highlighted
Creator
Creator

Re: Set analysis combined with if

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

Highlighted

Re: Set analysis combined with if

* 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