Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi I have a table, Contain the fields--> [Reporting Month], RAG, Numerator, Denominator, Ratio, Metric, KPI, Actual Value and Matric Value.
By these are the fileld I have created an expression and Now I ant to convert that expression in Script.
There is some set analysis features are available in expression label.
So can any one help me to convert the same expression to script. Below is my expression :
If(only({$<[Reporting Month]={"$(vCurrentMonthM0)"}, RAG2 = >}Len(RAG2)) = 0, Null(),
IF(only({$<[Reporting Month]={"$(vCurrentMonthM0)"}, RAG2 = >}[Metric Value]) = '%',
IF(only({$<[Reporting Month]={"$(vCurrentMonthM0)"}, RAG2 = >}(IF([Numerator2]='ND' OR [Numerator2]='PD',[Numerator2],NUM(Numerator2 ,'##,###'))))='ND'
OR only({$<[Reporting Month]={"$(vCurrentMonthM0)"}, RAG2 = >}(IF([Numerator2]='ND' OR [Numerator2]='PD',[Numerator2],NUM(Numerator2 ,'##,###'))))='PD'
OR only({$<[Reporting Month]={"$(vCurrentMonthM0)"}, RAG2 = >}(IF([Numerator2]='ND' OR [Numerator2]='PD',[Numerator2],NUM(Numerator2 ,'##,###'))))=NULL()
OR only({$<[Reporting Month]={"$(vCurrentMonthM0)"}, RAG2 = >}(IF([Denominator2]='ND' OR [Denominator2]='PD',[Denominator2],NUM(Denominator2 ,'##,###')))) = 'ND'
OR only({$<[Reporting Month]={"$(vCurrentMonthM0)"}, RAG2 = >}(IF([Denominator2]='ND' OR [Denominator2]='PD',[Denominator2],NUM(Denominator2 ,'##,###')))) = 'PD'
OR only({$<[Reporting Month]={"$(vCurrentMonthM0)"}, RAG2 = >}(IF([Denominator2]='ND' OR [Denominator2]='PD',[Denominator2],NUM(Denominator2 ,'##,###')))) = NULL()
OR only({$<[Reporting Month]={"$(vCurrentMonthM0)"}, RAG2 = >}(IF([Ratio2]='ND' OR [Ratio2]='PD',[Ratio2],NUM([Ratio2] * 100,'##.##') & '%'))) = 'ND'
OR only({$<[Reporting Month]={"$(vCurrentMonthM0)"}, RAG2 = >}(IF([Ratio2]='ND' OR [Ratio2]='PD',[Ratio2],NUM([Ratio2] * 100,'##.##') & '%'))) = 'PD'
OR only({$<[Reporting Month]={"$(vCurrentMonthM0)"}, RAG2 = >}(IF([Ratio2]='ND' OR [Ratio2]='PD',[Ratio2],NUM([Ratio2] * 100,'##.##') & '%'))) = NULL(),only({$<[Reporting Month]={"$(vCurrentMonthM0)"},
RAG2 = >}[Ratio2]),
if((GetSelectedCount([Reporting Month]) >=1 and GetSelectedCount(Country) >=1),
(only({$<[Reporting Month]={"$(vCurrentMonthM0)"}, RAG2 = >}(IF([Numerator2]='ND' OR [Numerator2]='PD',[Numerator2],
if(frac([Numerator2])<> 0, num(Numerator2,'###.##'),NUM(Numerator2 ))))) & ' / ' &
only({$<[Reporting Month]={"$(vCurrentMonthM0)"}, RAG2 = >}(IF([Denominator2]='ND' OR [Denominator2]='PD',[Denominator2],
if(frac([Denominator2])<> 0, num(Denominator2,'###.##'),NUM(Denominator2))))) & Chr(13) & '----------' & Chr(13) &
only({$<[Reporting Month]={"$(vCurrentMonthM0)"}, RAG2 = >}(IF([Ratio2]='ND' OR [Ratio2]='PD',[Ratio2],NUM([Ratio2] * 100,'##.##') & '%')))), NULL())),
if(Ref = 'KPI 53',
only({<[Reporting Month]={"$(vCurrentMonthM0)"}, [Metric Ref] = {'ABC 5'},RAG2 >}SubField(KPI53_Threshold,'|',1)&' (M)'& '+' & SubField(KPI53_Threshold,'|',2) & ' (HVH)'& Chr(13) & '----------' & Chr(13) & [Actual Value2] ),
only({$<[Reporting Month]={"$(vCurrentMonthM0)"}, RAG2 >}[Actual Value2]))))
Can anyone help me on this.
Regards,
AS
What is the purpose, you want to move that to script?
May be like below?
Table:
Load [Reporting Month] From Table;
Max:
Load Max([Reporting Month]) as MaxDate
Resident Table;
Let vMax = Peek('Max', 0, 'MaxDate');
Drop Table Max;
Left Join(Table)
Load [Reporting Month],
DATE(AddMonths($(vMax),0),'MMM-YY') as vCurrentMonthM0
Resident Table;
Got it. But there is some more challenges is there for set analysis. Can you help me on this.