Fiscal Year Syntax Error

[noumanbutt]

I'm using the following code in my script to generate a fiscal year field.

LET vFM = 7;
[Fisc]:
DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS

Dual(Right(YearStart($1,0,vFM),2)&'/'&(Right(YearStart($1,0,vFM),2)+1), YearStart($1,0,vFM)) AS [FiscalYear] Tagged ('$axis', '$year'),
Num(Dual('FY'&(Right(YearStart($1,0,vFM),2)+1), YearStart($1,0,vFM))) AS [FY_Num],
;

DERIVE FIELDS FROM FIELDS [TR_DATE] USING [Fisc] ;

I use the [FiscalYear] field for plotting and the [FY_NUM] to get max / current year or to sort the data.

However, If I use it to display in a table it works, but it's not working in set analysis nor does it return value if i use it in the KPI chart to test.

My Set Analysis code is following:

Count({$<[TR_DATE.Fisc.FY_Num]={"=$(=([TR_DATE.Fisc.FY_Num]))"}>} DISTINCT ID)

I get this message in the bottom which is telling me the FY_Num field is not returning the correct value in number format.

Can anyone identify the problem ?

[Vegar]

Your dolla expansion needs to reurn a value. Right now it is set to [TR_DATE.Fisc.FY_Num] which is the same thing as only([TR_DATE.Fisc.FY_Num] ). That expression will only return a value if you make selections so that only one value is associated with your selection.

Consider changing your modifier to max() , min().

Count({$<

[TR_DATE.Fisc.FY_Num]={"=$(=max([TR_DATE.Fisc.FY_Num]))"}

>} DISTINCT ID)

[noumanbutt]

Hi Vegar,

My apologies I missed putting in max while typing the code.

your code is exactly what I'm using and still get the same response.

Count({$<[TR_DATE.Fisc.FY_Num]={"=$(=max([TR_DATE.Fisc.FY_Num]))"}>} DISTINCT ID)

[PrashantSangle]

what is output of =max([TR_DATE.Fisc.FY_Num]) in KPI?

what is output of TR_DATE.Fisc.FY_Num in Filter pane?

Can you share screenshot of both ?

[diegozecchini]

Hi! main issue here seems to be the usage of $(...) syntax in set analysis. This syntax attempts to evaluate an expression and return its value. However, in this context, $(...) might not return a straightforward numeric value, especially when working with derived fields or when the evaluation doesn't happen as expected. Here’s how to approach it:

First, try simplifying the expression to isolate the issue:

Ensure FY_Num is correctly returning a numeric value. Try testing it in a text object using:

=Max([TR_DATE.Fisc.FY_Num])
This will verify if the FY_Num field is correctly populated with numbers.
If the above returns a valid number, modify your set analysis to use Max directly rather than a nested evaluation:

Count({$<[TR_DATE.Fisc.FY_Num]={"$(=Max([TR_DATE.Fisc.FY_Num]))"}>} DISTINCT ID)

Since the error message suggests a number format issue, make sure the field is recognized as a numeric value. Wrapping the evaluation in the Num() function can help enforce numeric formatting:
Count({$<[TR_DATE.Fisc.FY_Num]={"$(=Num(Max([TR_DATE.Fisc.FY_Num])))"}>} DISTINCT ID)
This ensures that the result is correctly treated as a number.

4. Check Field Naming in Set Analysis
Ensure that the field reference in the set analysis is correct. Depending on how derived fields are tagged, there might be differences in referencing. Adjust your set analysis field reference to:
Count({$<[FY_Num]={"$(=Max([FY_Num]))"}>} DISTINCT ID)
By simplifying the field name directly to [FY_Num] instead of [TR_DATE.Fisc.FY_Num], you avoid potential referencing issues caused by complex names.

Double-check that the derived fields FiscalYear and FY_Num are properly created and populated in the data model.
Verify the fiscal year generation script is functioning correctly by examining these fields in a table to ensure they contain the expected numeric values.

[Kushal_Chawda]

@noumanbutt 

Firstly, remove "=" before $ expansion as highlighted in your expression below. Because that "=" will be treated as part of search string between " ". Hence, when you evaluate max(FY_Num) let's say it is "45292", so your search string will become "=45292".  It interprets it as expression due to "=" sign instead search value, hence it will either return wrong value or blank.

Count({$<[TR_DATE.Fisc.FY_Num]={"=$(=([TR_DATE.Fisc.FY_Num]))"}>} DISTINCT ID) // Remove highlighted  "=".

Secondly, you can create your FY_Num field just as following as you already have text representation in [FiscalYear] field.

YearStart($1,0,vFM) AS [FY_Num]

Now, you can use below expression

Count({$<[TR_DATE.Fisc.FY_Num]={"$(=date(max([TR_DATE.Fisc.FY_Num])))"}>} DISTINCT ID)

[noumanbutt]

I have changed the variable name.

However, it gives the same result, with or without max.

[noumanbutt]

@diegozecchini you're correct as this field is not returning the correct numeric value or any value at all.

I haven't made any changes to the script and don't understand how it suddenly has started to return null value after the last update.

This is the expression as I'm currently using

Count({$<[EVDAT.Fisc.Year_Num]={"$(=([EVDAT.Fisc.Year_Num]))"}>} DISTINCT ID)

And the result in the bottom

Following is the load script for generating calendar values

LET vFM = 7; // first month of the fiscal year
[Fisc]:
DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
Dual(Right(YearStart($1,0,vFM),4)&'-'&(Right(YearStart($1,0,vFM),2)+1), YearStart($1,0,vFM)) AS [Year] Tagged ('$axis', '$year'),
Num(Dual(Right(YearStart($1,0,vFM),4)&'-'&(Right(YearStart($1,0,vFM),2)+1), YearStart($1,0,vFM))) AS [Year_Num],
Num(YearStart($1,0,vFM)) AS [FY_Num],

DERIVE FIELDS FROM FIELDS [EVDAT] USING [Fisc] ;

[noumanbutt]

The problem I believe is that the expression/field "[TR_DATE.Fisc.FY_Num]" is not returning any value which shouldn't be the case ... any suggestions ?

[Vegar · Accepted solution]

What if you adjust it to this:

[Fisc]:

DECLARE FIELD DEFINITION Tagged ('$date')

Parameters

vFM = 7

FIELDS

Dual(Right(YearStart($1,0,vFM),4)&'-'&(Right(YearStart($1,0,vFM),2)+1), YearStart($1,0,vFM)) AS [Year] Tagged ('$axis', '$year'),

Num(Dual(Right(YearStart($1,0,vFM),4)&'-'&(Right(YearStart($1,0,vFM),2)+1), YearStart($1,0,vFM))) AS [Year_Num],

Num(YearStart($1,0,vFM)) AS [FY_Num],

DERIVE FIELDS FROM FIELDS [EVDAT] USING [Fisc] ;