Syntax using FirstSortedValue in set Analysis

QFanatic — Tue, 30 Mar 2021 10:32:10 GMT

I have a model that contains FY in Dual format (created using Dual(fYear-1 &'/'& fYear, fYear) as FY).

In my Set analysis I want to count Distinct Periods where the PeriodID = max PeriodID for my FY(Financial year)

The syntax looks like this...

count({<Channel = {'Banking App'},CostName={'Prev_Yr_Actuals'},Table_Type = {'Rates'},FY = {'=firstsortedvalue(FY,-PeriodID)'}>}DISTINCT Period)

however, the statement doesnt resolve correctly..it resolves to:

FY={'=firstsortedvalue(FY,-PeriodID)'...

1.How do I correct this? Keep in mind my FY is dual...

2. Can I put this in a variable for re-use? If so - how would the syntax look like?

Much appreciated.

Re: Syntax using FirstSortedValue in set Analysis

rwunderlich — Tue, 30 Mar 2021 15:34:59 GMT

FY = {"$(=firstsortedvalue(FY,-PeriodID))"}

-Rob

QFanatic — Wed, 31 Mar 2021 07:02:11 GMT

Thanks Rob