topic Re: Text Object function in QlikView

Text Object function

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Text Object function

sunny_talwar — Thu, 02 Jun 2016 17:42:21 GMT

Doesn't look anything wrong, but may be this:

=If(Year(Today()) = [Period Year] and MonthStart(Today()) = [Period Name], [Period Name])

Re: Text Object function

Thu, 02 Jun 2016 18:04:12 GMT

Sunny T,
Period Name column has a format of Jan-16, Feb-16, Mar-16. This is why I was formatting it as MMM-YY.

Thanks,

Re: Text Object function

sunny_talwar — Thu, 02 Jun 2016 19:06:14 GMT

How about this:

=If(Year(Today()) = [Period Year] and MonthStart(Today()) = MonthStart(Date#([Period Name], 'MMM-YYYY')), [Period Name])

Re: Text Object function

Thu, 02 Jun 2016 19:20:22 GMT

It didn't work. All I get is "-" in the text box.

Re: Text Object function

CarlosAMonroy — Thu, 02 Jun 2016 19:35:23 GMT

Hi,

That expression is correct but it won't work unless you select a value from Period Year and Period Name. Looking into your expression I guess you want to show the current Period, said in Qlikview words the max period.

So You can try the following:

=Upper(Date(MonthStart(Today()), 'MMM-YY'))

or if you want to show based on the PeriodName field selection:

=if(GetSelectedCount([Period Name])=1,[Period Name])

Regards,

Carlos M

Re: Text Object function

Thu, 02 Jun 2016 20:38:20 GMT

Thanks Carlos for the expression but it didn't work either. Let me provide you with a bigger picture. I have a requirement where I have a [Start Date] and [End Date] for each fiscal period. if today() date falls between [Start Date]and [End Date] then give me [Period Name].

Hope it helps!

Thanks,

Re: Text Object function

santiago_respane — Thu, 02 Jun 2016 20:45:52 GMT

Hi,

as Carlos said, your expression doesn't work because it needs a value to be selected on that field.

This can be achieved with Set analysis, like this:

=MAX( {<[Period Year]={"$(=YEAR(TODAY(1)))"},[Period Name]={"$(=MONTH(TODAY(1)))"}>} [Period Name])

This will show the period if you have a value for the current period, kind of what you are asking because if you want to show current period i would go with Carlos option.

Please let me know if this helps.

Kind regards,

Re: Text Object function

sunny_talwar — Fri, 03 Jun 2016 01:53:51 GMT

Would you be able to share a sample to look at?

Re: Text Object function

Fri, 03 Jun 2016 13:18:21 GMT

Sunny T,

I have attached the file. I am trying to see if the current date falls between start and end date then give me Period Name. It is working in List Box but it won't work on the Text Box.

Thanks,

Re: Text Object function

sunny_talwar — Fri, 03 Jun 2016 13:49:22 GMT

Try this:

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

Re: Text Object function

suepenick — Fri, 03 Jun 2016 14:24:25 GMT

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]);