Solved: How to Create a Text Value for a label out of an I...

Ray_Bibby · 12-10-2020 07:25 PM

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

MultiTech

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?

View solution in original post

LeventK

@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?

Ray_Bibby

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”.

Steve

For reference:

MultiTech

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?

Ray_Bibby

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.

Bahbus

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

MultiTech

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.

Ray_Bibby

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)

Ray_Bibby

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

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