topic Re: Convert If condition in set analysis in QlikView

Convert If condition in set analysis

Anonymous — Mon, 06 Jan 2014 07:02:40 GMT

Hi All,

I would like to convert the below if-else condition in set analysis.

sum(if(It.isAdj and It.QTY=0,0,It.Qty * It.factor))

I tried to convert as below using set analysis but it is not giving the result as the above expression gives. The set analysis I tried is:

sum({<It.isAdj = {0},It.QTY-= {0}>}It.Qty * It.factor)

This set analysis is giving the result equal to the below expression:

sum(if(not It.isAdj and It.QTY> 0,It.Qty * It.factor))

So I need to convert this sum(if(It.isAdj and It.QTY=0,0,It.Qty * It.factor)) expression to set analysis where both should give me the same result.

Can anyone help me in this?

Thanks,

Leni Balakrishnan

Re: Convert If condition in set analysis

vikasmahajan — Mon, 06 Jan 2014 07:05:00 GMT

Hii

Pleae find expr using if condition in set analysis

fabs(SUM(AGGR(if(((vSelectDate - max({$<StockInOutIndicator={1}>}PostingDate) >=271)),SUM(Inv_Val)),Location_Code, Item_Code, [Lot No_]))/100000)

Vikas

Re: Convert If condition in set analysis

Mon, 06 Jan 2014 07:21:20 GMT

Hi Leni ,

Your Expressions on Set Analysis are perfect .. U don't need to change into Set analysis ..

Those are perfect.

Regards,

koti Reddy

Re: Convert If condition in set analysis

Anonymous — Mon, 06 Jan 2014 07:35:16 GMT

But it is not giving me the exact result of the if condition.

sum(if(It.isAdj and It.QTY=0,0,It.Qty * It.factor)) in set analysis = sum({<It.isAdj = {0},It.QTY-= {0}>}It.Qty * It.factor) but both returns different value.

Re: Convert If condition in set analysis

brijesh1991 — Mon, 06 Jan 2014 07:39:15 GMT

Why you want to convert into set analysis?

It seems you do not need to convert it. . . Expression you used is fine!

Set Analysis is used when you wanted to give explicit selection on a field(even though you don't select from front end)

Re: Convert If condition in set analysis

Mon, 06 Jan 2014 07:48:58 GMT

= sum({<It.isAdj = {0},It.QTY-= {0}>}It.Qty * It.factor) it is not giving the same value because

the expression says if It.isAdj = {0} and It.QTY-= {0} (except 0 for remaining all Values ) the result would be

this ---> It.Qty * It.factor

And it is not equal to this----> sum(if(It.isAdj and It.QTY=0,0,It.Qty * It.factor))

there are lot of conditions if both are zeros then the expression should Be 0 . So for your situation it is better to use same if expression ..

Re: Convert If condition in set analysis

Anonymous — Mon, 06 Jan 2014 09:06:16 GMT

Why I am converting this expression using set analysis is,

I have two charts X & Y where the expression & filters for both the charts are same except one which is Cust. Name. Chart X should show details of all customers but Chart Y should show only a Customer details.

So I thought of converting the expression so that Chart X will not change for the selection on Customer Name.

Kindly suggest me an idea or your opinion to achieve this scenario

Thanks

Re: Convert If condition in set analysis

Mon, 06 Jan 2014 09:20:34 GMT

Until there is relationship between dimensions(Cust , Custname) in datamodel it is obviously changes based on filter selections

If u want to not affect the selections of Chart X in Chart Y .. Then Create Alternate States and Provide Separate filters

and apply Alternate states to that particular Objects..

Hope it helps..

Re: Convert If condition in set analysis

Anonymous — Mon, 06 Jan 2014 09:22:32 GMT

Hi Brijesh,

I have explained my scenario in the above post and the reason why I am trying to convert to set analysis.

Please look into that and share your views.

Thanks

Re: Convert If condition in set analysis

Anonymous — Mon, 06 Jan 2014 10:20:17 GMT

Hi Koti,

Thanks for your suggestion.

I am not familiar with the Alternate states concept. Could you please explain me on how it works?.

Before that let me explain my full requirement here so that you could guide me properly.

I need to send chart X & Y in mail through excel attachment and it will not be available for the enduser through portal. It is used only to send mail.

I have a .bat file which I have scheduled through Windows Task scheduler to trigger the mail on a particular time. When the .bat file is triggered it will open my application, make the required filters, export the data to excel from those charts, save the excel file, the saved excel file is attached to the mail and sent to the requested users.

This is my requirement and in this I have difficulty in applying the filters to chart which I explained above.

Thanks

Re: Re: Convert If condition in set analysis

Mon, 06 Jan 2014 12:08:46 GMT

Hi Leni,

Alternate States Available only from Qlikview 11 Onwards..

Go to Document Properties -->General--> Alternate states->Click On It-->Click on Add->Enter Statename(Some String) State1-> Ok Like that Create Two states..

Go To Object Properties..Provide Statename to All the Objects which U want filters for First chart

Do the Same for Second Chart And You can Make Selections for charts you are showing...

Otherwise You Can Detach the Chart from Dashboard Right Click on chart and Click On Detach the Chart.. This Also does not effect the Selection of Filters into Chart.

May be It is perfect for Ur Problem..

If U want to check How Alternate States work ...In Qlikview 11 Developer-->Open Whats new in Qlikview11 Dashboard..

Product Grouping Tab..

May be It will not be What r u looking for.. It will help to Achieve it....

Check Out This Vedio Comparative Analysis - YouTube

And PFA. I hope It Helps...

Re: Convert If condition in set analysis

Anonymous — Mon, 06 Jan 2014 12:43:05 GMT

Thanks for your suggestion. It worked

Thanks a lot.

and I tried as below and this also achieved my requirement.

sum({<ct.cust_name=>}if(it.isAdj and it.QTY=0,0,it.Qty * it.Factor))