Text Box Expression

[upaliwije]

In a text box I have following expression and is working perfectly

=NUM(count (DISTINCT{$<LEGAL={'NOT_LEGAL'},FYear={"$(=Only([FYear]))"}>}CLAIM_NO),'#,##0.')

=NUM(count (DISTINCT{$<LEGAL={'NOT_LEGAL'},FYear={"$(=Only([FYear])-1)"}>}CLAIM_NO),'#,##0.')

But When I modify the expression with -1 it is not working.

My FYear list box has following data

2011/2012

2012/2013

2013/2014

Is any thing wrong with my expression. Pls help

[simondachstr]

Your FYear list box will be interpreted as string.. substracting 1 from it will result to null.

Instead of only(), you should be using the FirstSortedValue() functionality.

[upaliwije]

Thanks

I don't understand it much . Can u modify me expression and let me know pls

[simondachstr]

Try

=NUM(count (DISTINCT{$<LEGAL={'NOT_LEGAL'},FYear={"$(=FirstSortedValue([FYear],-[FYear],1))"}>}CLAIM_NO),'#,##0.')

=NUM(count (DISTINCT{$<LEGAL={'NOT_LEGAL'},FYear={"$(=FirstSortedValue([FYear],-[FYear],2))"}>}CLAIM_NO),'#,##0.')

[MK_QSL · Accepted solution]

=NUM(count (DISTINCT{$<LEGAL={'NOT_LEGAL'},FYear={"$(=(Left(Only(FYear),4)-1) & '/' & (Right(Only(FYear),4)-1))"}>}CLAIM_NO),'#,##0.')

[upaliwije]

Thanks all

[simondachstr]

That expression is very calculation intensive. A string is being split apart and then concatenated back into one.

[upaliwije]

Tks,

But your one does not give desired results

[simondachstr]

I agree it doesn't work, First Sorted Value needs a numeric value to sort on which is not the case in FYear.

Instead, I recommend you to create an additional numeric field in the script (maybe FY_ID using AutoNumber()) and filter your set analysis based on that.

[upaliwije]

Thanks for your continuous help.

Sorry for the delay in replying. If you can let me know how to create Autono with an example I would appreciate very much. I am attaching my script my FYear  Calender script and pls incorporate autono script into same.

Set vFM = 9 ;                                                          // First month of fiscal year

Calendar:

Load Dual(fYear-1 &'/'& fYear, fYear) as FYear,          // Dual fiscal year

         Dual(MONTH, fMonth)                as FMonth,           // Dual fiscal month

          *;

Load YEAR + If(MONTH>=$(vFM), 1, 0) as fYear,           // Numeric fiscal year

         Mod(MONTH-$(vFM), 12)+1        as fMonth, 

        * ;    

   load 

     POL_PERIOD_TO,      

     Day(POL_PERIOD_TO) AS DAY,

    Date(POL_PERIOD_TO) AS DATE,

    Week(POL_PERIOD_TO) AS WEEK,

   Year(POL_PERIOD_TO) AS YEAR,

    Month(POL_PERIOD_TO) As MONTH,

    Month(POL_PERIOD_TO) &'-'& week(POL_PERIOD_TO) As Month_Week;

  Load Date(TRN_MinDate + IterNo() -1 ) AS POL_PERIOD_TO While (TRN_MinDate + IterNo() - 1) <= Num(TRN_MaxDate);

   Load

   Min(POL_PERIOD_TO) AS TRN_MinDate,

    Max(POL_PERIOD_TO) AS TRN_MaxDate  

RESIDENT Link_Table;