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

[Shubhangi_01]

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

[Anil_Babu_Samineni]

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;

[Shubhangi_01]

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

[marcus_sommer]

What happens with this:

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

- Marcus

[Shubhangi_01]

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 :

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

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

Thanks,

Shubhangi

[marcus_sommer]

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)

[Shubhangi_01]

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([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

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

Can you please help.

Thanks, 

Shubhangi

[marcus_sommer]

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

[Shubhangi_01]

Hi Marcus,

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

Can someone please help.

Thank you,

Shubhangi 

[marcus_sommer]

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