Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

leni_balakrishn
Contributor III

Convert If condition in set analysis

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

Tags (2)
1 Solution

Accepted Solutions
Not applicable

Re: Re: Convert If condition in set analysis

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

11 Replies
vikasmahajan
Honored Contributor III

Re: Convert If condition in set analysis

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

Not applicable

Re: Convert If condition in set analysis

Hi Leni ,

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

Those are perfect.

Regards,

koti Reddy

leni_balakrishn
Contributor III

Re: Convert If condition in set analysis

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.


brijesh1991
Contributor III

Re: Convert If condition in set analysis

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)

Not applicable

Re: Convert If condition in set analysis

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





leni_balakrishn
Contributor III

Re: Convert If condition in set analysis

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

Not applicable

Re: Convert If condition in set analysis

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

leni_balakrishn
Contributor III

Re: Convert If condition in set analysis

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

leni_balakrishn
Contributor III

Re: Convert If condition in set analysis

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

Community Browser