topic Converting set analysis expression to if statement (edit script window) in QlikView

Converting set analysis expression to if statement (edit script window)

Shubhangi_01 — Tue, 10 Mar 2020 10:42:26 GMT

Hello Everyone,

I am facing one issue, where my chart keeps refreshing continuously. When i checked i got to know that because I have used complex date expression in my set analysis it is getting refreshed continuously.

I have used the following expression to calculate my data in chart:

=COUNT({<[ProjectDecision]-={'Hold','Kill'},[Stage]-={'Launch'},
[Status]-={'Closed'},[ModelName]-={'YHE','OPG'},[LaunchDate] ={"<=$(=date#(Now(), 'yyyy-MM-dd'))"}>}
if(isnull([LaunchDate]),0,1))
/COUNT(DISTINCT ProjectID)

Can you please help me in converting this expression into Script expression.

I tried the below expression, but it is giving 0 :

Script in Edit Script :

if([ProjectDecision]='Kill',-1,
if([ProjectDecision]='Hold',-1,
if(Stage='Launch/Tracking',-1,
if(alt(if([ModelName]<>'YHE','',0),if([ModelName]<>'OPG','',0),1)=1,-1,
if([Status]='Closed',-1,
if(alt(date([LaunchDate]),'')='',-1,if(num(date([LaunchDate]))-num(date(Today()))<0,1,0
))))))) as ABC

Script in Chart (set analysis) :

=if(Count(if(ProjectID>0,if(ABC>=0,ProjectID)))=0,'',count(if(ProjectID>0,if(ABC>=0,ABC)))/Count(if(ABC>=0,ProjectID)))

Please let me know if you require any more information.

Please help.

Thanks in advance.

Regards,

Shubhangi

Re: Converting set analysis expression to if statement (edit script window)

Anil_Babu_Samineni — Tue, 10 Mar 2020 10:54:18 GMT

How about this?

Table:
Load ProjectDecision,
Stage,
Status,
ModelName,
LaunchDate,
ProjectID
From Table;

Left Join (Table)

Load LaunchDate,
Count(If(IsNull(LaunchDate),0,1))/Count(ProjectID) as Count_Form
Resident Table Where Not Match(ProjectDecision,'Hold','Kill') and Stage<>'Launch' and Status<>'Closed' and Not Match(ModelName, 'YHE', 'OPG') Group By LaunchDate;

Re: Converting set analysis expression to if statement (edit script window)

Shubhangi_01 — Tue, 10 Mar 2020 11:43:33 GMT

Hi,

Thank you for your reply.

I tried your solution, but it is giving me incorrect syntax error.

Is there any other way to solve this.

Thanks,

Shubhangi

Re: Converting set analysis expression to if statement (edit script window)

marcus_sommer — Tue, 10 Mar 2020 13:48:32 GMT

What happens with this:

=COUNT({<[ProjectDecision]-={'Hold','Kill'},[Stage]-={'Launch'},
[Status]-={'Closed'},[ModelName]-={'YHE','OPG'},[LaunchDate] ={"<=$(=today())"}>} [LaunchDate])
/COUNT(DISTINCT ProjectID)

- Marcus

Re: Converting set analysis expression to if statement (edit script window)

Shubhangi_01 — Tue, 10 Mar 2020 13:58:24 GMT

Hi Marcus,

This is giving me the required data as I want.

=COUNT({<[ProjectDecision]-={'Hold','Kill'},[Stage]-={'Launch'},
[Status]-={'Closed'},[ModelName]-={'YHE','OPG'},[LaunchDate] ={"<=$(=today())"}>} [LaunchDate])
/COUNT(DISTINCT ProjectID)

I want to have the same query in if statement for edit script window, so that i can have the required data while loading itself.

I guess, syntax wise I am going some where wrong into this :

Script in Edit Script :

Script in Chart (set analysis) :

=if(Count(if(ProjectID>0,if(ABC>=0,ProjectID)))=0,'',count(if(ProjectID>0,if(ABC>=0,ABC)))/Count(if(ABC>=0,ProjectID)))

If you can find the error, please let me know.

Thanks,

Shubhangi

Re: Converting set analysis expression to if statement (edit script window)

marcus_sommer — Tue, 10 Mar 2020 14:46:05 GMT

I think it should rather look like:

if(match([ProjectDecision], 'Kill', 'Hold') or
    Stage='Launch/Tracking' or
    match([ModelName], 'YHE', 'OPG') or
   [Status] = 'Closed' or
   not isnum([LaunchDate]) or
   [LaunchDate] >= Today(), 0, 1) as ABC

Script in Chart (set analysis) :

sum(ABC) / Count(distinct ProjectID)
or maybe
count({> ABC = {1}>} [LaunchDate]) / Count(distinct ProjectID)

Re: Converting set analysis expression to if statement (edit script window)

Shubhangi_01 — Wed, 11 Mar 2020 10:23:16 GMT

Hi Marcus,

Thank you for your help!

I tried the solution which you gave, but i want to have the ProjectID where all the records having following conditions are excluded.

If you observe my script, i am trying to exclude the projects having all this conditions.

Can you please help.

Thanks,

Shubhangi

Re: Converting set analysis expression to if statement (edit script window)

marcus_sommer — Wed, 11 Mar 2020 11:52:55 GMT

My suggestion is just the attempt to simplify the approach by conflating the multiple if-loops. I'm not sure if I "translated" your example (did it work?) correctly - but I think you could use it as starting point to develop the flag-field. Before you apply the deduced expression I suggest to use a tablebox with all related fields to check if the logic worked and to make the needed adjustment if it didn't.

- Marcus

Re: Converting set analysis expression to if statement (edit script window)

Shubhangi_01 — Thu, 12 Mar 2020 08:17:14 GMT

Hi Marcus,

The solution which you gave dint worked. I am getting wrong data.

Can someone please help.

Thank you,

Shubhangi

Re: Converting set analysis expression to if statement (edit script window)

marcus_sommer — Thu, 12 Mar 2020 08:47:34 GMT

Did you check the data and the calculated flag within a tablebox? Have you checked the flag-creation and the following expressions partly - maybe it's just a very small issue by one of the parts.

- Marcus

Re: Converting set analysis expression to if statement (edit script window)

Brett_Bleess — Tue, 07 Apr 2020 19:25:28 GMT

Were you able to see if Marcus' last comments helped or not? Please be sure to circle back and close out the thread if you got things sorted or let us know what you still need if you are still working on things.

Regards,
Brett

Re: Converting set analysis expression to if statement (edit script window)

Shubhangi_01 — Thu, 09 Apr 2020 12:53:38 GMT

Hi,

The solution which Marcus gave dint helped me.

But I did some research and solved it in a different way.

Thank you so much for your help and support.

Thanks,

Shubhangi