Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everybody!
Thanks for taking the time to read this post. I need to make the words in a text box change based on the user's selection. I have a master calendar that links fiscal year, fiscal quarter, and fiscal month with my data. I need the words to represent the selection. So if a user selects 2014, the words need to say 2014. If the user selects March and 2014, the words need to say Mar-14 (like how it's formatted in my FiscalMonthYear column in my master calendar), and so on and so forth. I've tried these possibilities and have gotten the following results:
//displays -1 =if(([Fiscal Year] or FiscalMonthYear or FiscalQuarter or FiscalQuarterYear or FiscalMonth), (FiscalYear or FiscalMonthYear or FiscalQuarter or FiscalQuarterYear or FiscalMonth), '-')
// displays - =GetFieldSelections(FiscalMonthYear)
// displays - =if(GetFieldSelections(FiscalMonthYear), FiscalMonthYear)
// displays -1 =[Fiscal Year] or FiscalMonth or FiscalMonthYear or FiscalQuarter or FiscalQuarterYear
//displays -1 =if([Fiscal Year], [Fiscal Year]) or if(FiscalMonthYear, FiscalMonthYear)
// displays -1 =if(isnull(FiscalMonthYear or [Fiscal Year] or FiscalMonth or FiscalQuarter or FiscalQuarterYear),'Fiscal Period', FiscalMonthYear or [Fiscal Year] or FiscalMonth or FiscalQuarterYear or FiscalQuarter)
//= displays - GetFieldSelections(FiscalMonthYear or [Fiscal Year] or FiscalMonth or FiscalQuarter or FiscalQuarterYear)
//displays -1 =if([Fiscal Year] or FiscalMonthYear or FiscalQuarter or FiscalQuarterYear or FiscalMonth, FiscalYear or FiscalMonthYear or FiscalQuarter or FiscalQuarterYear or FiscalMonth, '-')
// displays year =if([Fiscal Year] or FiscalMonthYear, [Fiscal Year] , FiscalMonthYear)
Thanks in advance for your help!
It may be just a question of how you prioritize the fields you want to show:
In my point of view, FiscalMonthYear is more granular / specific than a Month or Year:
=if( len(FiscalMonthYear), FiscalMonthYear,
if( len(FiscalQuarterYear), FiscalQuarterYear,
if( len(FiscalMonth), FiscalMonth,
if(len(FiscalQuarter), FiscalQuarter,
if( len([Fiscal Year]), [Fiscal Year])
))))
I am a little unsure how to get the quarters in sequence, so you may want to rotate things a little.
If all your fields have a numeric representation, you can use something like
=alt(Date, YearMonth, Year)
It's important that you order the arguments finest granularity first, largest granularity last, so it will display Date when a single date is available / selected, then check for single YearMonth, then check for single Year.
If you have non-numeric values, same would apply for nested if() statements:
=if( len(Date), Date,
if( len(YearMonth), YearMonth,
if(len(Year), Year)
))
That definitely got me closer! Thank you! Now I have:
=if(len(FiscalMonth), FiscalMonth,
if(len(FiscalQuarter), FiscalQuarter,
if( len([Fiscal Year]), [Fiscal Year],
if( len(FiscalMonthYear), FiscalMonthYear,
if( len(FiscalQuarterYear), FiscalQuarterYear,)))))
But it will only show one thing at a time even if multiple selections are made. So if I select 2014, it will display 2014. If I select January, it will display Jan. But if I select both, it will just display Jan. What am I doing wrong? Thanks again!
It may be just a question of how you prioritize the fields you want to show:
In my point of view, FiscalMonthYear is more granular / specific than a Month or Year:
=if( len(FiscalMonthYear), FiscalMonthYear,
if( len(FiscalQuarterYear), FiscalQuarterYear,
if( len(FiscalMonth), FiscalMonth,
if(len(FiscalQuarter), FiscalQuarter,
if( len([Fiscal Year]), [Fiscal Year])
))))
I am a little unsure how to get the quarters in sequence, so you may want to rotate things a little.
That worked! Final question: how do I add an if no selection, display Fiscal Period? I tried writing:
=if(isnull('Fiscal Period'),
if(len(FiscalMonthYear), FiscalMonthYear,
if(len(FiscalQuarterYear), FiscalQuarterYear,
if(len(FiscalMonth), FiscalMonth,
if(len(FiscalQuarter), FiscalQuarter,
if(len([Fiscal Year]), [Fiscal Year]))))))
If there is no selection, is there only one single possible value for Fiscal Period?
If there are multiple values possible, which one should be shown?
Sorry, I think it was unclear. Right now if there is no selection, it displays a hyphen. I want the words "Fiscal Period" to be there instead of - . Any ideas?
=if( len(FiscalMonthYear), FiscalMonthYear,
if( len(FiscalQuarterYear), FiscalQuarterYear,
if( len(FiscalMonth), FiscalMonth,
if(len(FiscalQuarter), FiscalQuarter,
if( len([Fiscal Year]), [Fiscal Year], 'Fiscal Period' )
))))
For some reason it displays 2014 instead of Fiscal Period. I don't know why as that doesn't make any sense. I triple-checked to make sure all the selections were reset. Any idea why? Thank you so much for your help!
The epression does not check explicitely for selected values, just for single possible values.
How many possible FY do you have in your app? Only a single one, 2014?