Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
=NUM(count (DISTINCT{$<LEGAL={'NOT_LEGAL'},FYear={"$(=(Left(Only(FYear),4)-1) & '/' & (Right(Only(FYear),4)-1))"}>}CLAIM_NO),'#,##0.')
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.
Thanks
I don't understand it much . Can u modify me expression and let me know pls
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.')
=NUM(count (DISTINCT{$<LEGAL={'NOT_LEGAL'},FYear={"$(=(Left(Only(FYear),4)-1) & '/' & (Right(Only(FYear),4)-1))"}>}CLAIM_NO),'#,##0.')
Thanks all
That expression is very calculation intensive. A string is being split apart and then concatenated back into one.
Tks,
But your one does not give desired results
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.
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;