Text Object function

[#1]

Hi Everyone,

I have a requirement where I need to display fiscal month period in a text Object. I have tried the following expression in list box and it works perfectly but the same expression fails to work in the Text Box. The list box is displaying a single value and I need to display the same value in Text Object.

=if(Year(TODAY())=[Period Year]AND Upper(Date(MonthStart(Today()), 'MMM-YY'))=[Period Name],[Period Name])

How should I write this expression in the Text Object so It can bring back the fiscal period name?

Thanks,

ZQ

[sunny_talwar · Accepted solution]

Try this:

=Only(if(Year(TODAY())=[Period Year]AND Upper(Date(MonthStart(Today()), 'MMM-YY'))=[Period Name],[Period Name]))

[suepenick]

Zeeshan,

The simplest way is to create a spread sheet of your beginning and ending dates for each Fiscal Period

then do an interval match in the load script to get today's period.  Load Today's actual date in a field called TodaysDate.

You will use this simple logic in every QVW you ever make, because you will match an invoice date or a transaction date; any date - to get which period it was transacted in or when it is due.  These become dimensions in your QVW's for making pivot tables etc.  Since we actually have 3 year types, Model Year, Fiscal Year and Marketing Year that all begin and end in different months, this is the easiest way to define them.  I use the Month or Year ID field for doing rolling months expressions and Year/over/Year comparisons.  I name my Fiscal Periods in the spread sheet FP01, FP02... so they sort in order because the month name alpha sort puts them all awry. 

//GetTodaysDate

TodaysDate = Today()

CalendarMatchOnDate:

IntervalMatch(TodaysDate)

LOAD  Beginning, EndDate

FROM

[..\..\..\Common\SourceData-Excel\CompanyCalendar.xlsx]

(ooxml, embedded labels, table is [Beg End Dates]);

CompanyCalendar:

LOAD

  Beginning,

  EndDate,

  MonthName,

  FiscalPeriod,

  FiscalYear,

  FiscalQtr,

  WeekOfMonth,

  WeekOfFiscalYear,

  MonthID,

  YearID

FROM

[..\..\..\Common\SourceData-Excel\CompanyCalendar.xlsx]

(ooxml, embedded labels, table is [Beg End Dates]);