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]

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

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

[#3]

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,

ZQ

[sunny_talwar]

How about this:

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

[#5]

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

[CarlosAMonroy]

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

[#7]

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,

ZQ

[santiago_respane]

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,

[sunny_talwar]

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

[#10]

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,

ZQ