How to Create a Text Value for a label out of an IFS() Expression

I am new to AppSheet but I am trying to create a “Label” Out of an IFS() expession. Basically if it returns True I want a Label that says “This Pay Period”. I am pulling the info from a range of dates and comparing them to a list of dates. I have tried everything I can think of from the simple Display Value to changing the formula. On my app View it just says [Blank]?
Here is what I have so far.

IFS([Date] >= any(select(Pay Periods[Start Date],True)), [Date] <= any(select (Pay Periods[End Date], true)))

1 Like

@Ray_Bibby
ANY() expression pulls out the very first item in a list. So provided you are trying to sort out particular date range, this may not work as expected.

May I ask what exactly do you mean with the word: “Label”? Are you trying to create a table grouping header or are you trying to construct a label for a table to use as the REF column labelling?

3 Likes

For reference:



2 Likes

Yes I am trying to create a table grouping header for “This Pay Period” and then another for “Last” or “Past pay Periods” and maybe even have it track the hours worked for the “This Pay Period”.

1 Like

Hey @Ray_Bibby

This is something I do all the time, here’s the general setup I typically have for the following scenario:

  • I have a table that holds records of Timesheets
    • Inside that table, there is a Date column
  • I have a table that holds the start and end date of each pay period
    • I have made a slice pulling out the “Current_Pay_Period” with a formula like this:

And(
TODAY() >= [Pay_Period_Start_Date],
TODAY() <= [Pay_Period_End_Date]
)

If I wanted to create a label for each Timesheet row, that showed whether or not the timesheet was inside the “Current_Pay_Period” slice, I would use a formula like this:

IFS(and(
  [Timesheet_Date] >= INDEX(Current_Pay_Period[Pay_Period_Start_Date], 1), 
  [Timesheet_Date] <= INDEX(Current_Pay_Period[Pay_Period_End_Date], 1)
),
  "This Pay Period"
)

This will either have the text shown above (if the condition is TRUE), or it will be blank (if the condition is FALSE - meaning the timesheet date is NOT inside the current pay period).


Is that something like what you were thinking?

1 Like

Perfect that is exactly what I am looking for However I still am not getting a Heading for the group Just a Y? Like This.

I do this in a two step process (for easy changes).
image

image

image

image

1 Like

That would be because the ending portions of your If statements are conditions, not text values.

If you look at the sample formula I gave, you’ll see that the “true” protion of the IF() is a text value; where the “true” value for Daves

his is a condition.

Conditions result in either TRUE or FALSE - that’s why you see a Y/N.

This is great @MultiTech_Visions , I have used the Formula and it works great, then I added to it a bit to make a seperate heading for “Past Pay Periods” and when I test it the formula works great but I get a heading of “Blank” again instead of “Past Pay Periods”

Here is the formula I have
IFS(and(
[Date] >= INDEX(Current Pay Period[Start Date], 1),
[Date] <= INDEX(Current Pay Period[End Date], 1)
),
“This Pay Period”
,
and( [Date] >= INDEX(Current Pay Period[Start Date], 2),
[Date] <= INDEX(Current Pay Period[End Date], 2)
),
“Past Pay Periods”

)

Untitled.pdf (62.1 KB)

1 Like

By the way thank you everyone @LeventK, @MultiTech_Visions, @Steve, @Bahbus. Great info and great Community.

2 Likes