topic if statement with set analysis in QlikView

if statement with set analysis

jood_ahmad — Fri, 04 Mar 2016 15:32:55 GMT

Dears

if i have like an 3 Orders contain a like 4 type of pizza L,M,S i need to get the total Quantity for large pizza so i use this

=Sum({<PAYMENT_METHOD -= {'Invitation'},ITEM_FAMILY_GROUP ={[Large Pizza]}>}QUANTITY)

but if i need to get the Large Pizza Quantity from all orders that only contain more than on Large Pizza; i try to add the if condition based on the Order Number but it doesn't work , i feel confused regarding how can i put if condition inside set analysis.

this the data sample :

Message was edited by: Ahmad Kastero with order key

if you see the second document New Microsoft Excel Worksheet.xlsx and filter on order 86 you can see there is more than on Large Pizza so for this order i will take the count of large pizza item and the rest of orders how contain only one large pizza i will ignore it .

Message was edited by: Ahmad Kastero

Re: if statement with set analysis

-SW- — Fri, 04 Mar 2016 15:56:08 GMT

Hi Ahmad,

I dont quite understand your question, can you explain it more in what output you expect to get?

The only thing i can think of here, is your [If Condition] field can be added into your set analysis like this:

=Sum({<PAYMENT_METHOD -= {'Invitation'},ITEM_FAMILY_GROUP ={[Large Pizza]},[If condition]={'1'}>}QTY)

Thanks.

Re: if statement with set analysis

sunny_talwar — Fri, 04 Mar 2016 15:58:52 GMT

Ahmad Kastero wrote:

but if i need to get the Large Pizza Quantity from all orders that only contain more than on Large Pizza; i try to add the if condition based on the Order Number but it doesn't work , i feel confused regarding how can i put if condition inside set analysis.

I don't understand this part. What are you trying to do? What is the expected output?

Re: if statement with set analysis

satishkurra — Fri, 04 Mar 2016 16:05:03 GMT

See if this suits your requirement?

Re: if statement with set analysis

Anonymous — Fri, 04 Mar 2016 16:10:58 GMT

Not sure I completely understand what you are trying to do, but from the data set that was provided in the sample excel file, you can do something like this to get Large Pizzas, where the QTY was greater than 1.

sum( { < ITEM_FAMILY_GROUP={'Large Pizza'}, QTY={'>1'} >} QTY)

*Note that the sample dataset didn't include PAYMENT_METHOD so I didn't include it on the set analysis above because I don't know what the data looks like.

Re: if statement with set analysis

jood_ahmad — Fri, 04 Mar 2016 16:11:04 GMT

hi Sunny ,

i try to get the Quantity of pizza from all my orders but this number i need it only for that orders contain more than one Large Pizza; if my orders contain one large pizza i will not count it if more i will count it.

Re: if statement with set analysis

jolivares — Fri, 04 Mar 2016 16:12:38 GMT

I Think you can simple add:

=Sum({<PAYMENT_METHOD -= {'Invitation'},ITEM_FAMILY_GROUP ={[Large Pizza]}>} If(QUANTITY>1,QUANTITY))

Re: if statement with set analysis

sunny_talwar — Fri, 04 Mar 2016 16:24:09 GMT

Some of the fields are missing in your sample file (PAYMENT_METHOD). But is this what you are looking for?

=Sum({<ITEM_FAMILY_GROUP = {'Large Pizza'}, QTY = {'>1'}>} QTY)