I am facing an issue with Tableau CRM (Einstein Analytics) SQAL :
 
I have data like that two columns like that
 
StageName       Value      FScore
 
Qualified            166        0.7108433734939759
Quoted               118       0.6271186440677966
Committed          74        0.2
Closed Lost          4        0.25
Closed Won          1        -
 
I have to fetch the FSCore following basis :
 
Qualified =  Quoted / Qualified            
Quoted=  Committed / Quoted
Committed  =   closed won /(closed won + closed lost)   [ only change for committed]
Closed Lost   =  Closed Won / Closed Lost      
Closed Won   =   null      
 
I have achieved the FScore using the following SAQL using compare table:
 
q = load "Pipeline_Data_Latest";
result = group q by 'Stage_Name' ;
result = foreach result generate q.'Stage_Name' as 'Stage_Name',
sum(q.'Opportunity_Count') as 'A',
(
case when  q.'Stage_Name'=="Committed" then
                   
(sum(sum(q.'Opportunity_Count')) over ([2..3] partition by all order by sum(q.'Opportunity_Count') desc)
/ (sum(sum(q.'Opportunity_Count')) over ([1..3] partition by all order by sum(q.'Opportunity_Count') desc)))
else
(sum(sum(q.'Opportunity_Count')) over ([1..1] partition by all order by sum(q.'Opportunity_Count') desc) / (sum(sum(q.'Opportunity_Count')) over ([0..0] partition by all order by sum(q.'Opportunity_Count') desc)))
end  
) as 'FinalScore',
(
case when  q.'Stage_Name'=="Committed" then
(sum(sum(q.'Opportunity_Count')) over ([2..3] partition by all order by sum(q.'Opportunity_Count') desc)
/ (sum(sum(q.'Opportunity_Count')) over ([1..3] partition by all order by sum(q.'Opportunity_Count') desc)))
else
(sum(sum(q.'Opportunity_Count')) over ([1..1] partition by all order by sum(q.'Opportunity_Count') desc) / (sum(sum(q.'Opportunity_Count')) over ([0..0] partition by all order by sum(q.'Opportunity_Count') desc)))
end    
) as 'FinalScoreNew'
;
result = order result by ('A' desc);
result = limit result 2000;
 
working fine but issue is with when the position is being changed from that place i.e.
 
StageName       Value      FScore
Qualified            166        0.7108433734939759
 
Quoted               118       0.6271186440677966
 
Committed          74        (Gives wrong result)
 
Closed Won          1        .25
 
Closed Lost          4        -
 
so question is that I am not able to get only value for Closed Won and Closed Lost row so that it will be fixed for row based value. I have tried to fetch using the another result set but was not able to do so. even I was wondering how filtering is possible with windowing function plus how to use foreach or strem output in another strem or foreach. Please suggest.