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)))
Solved! Go to Solution.
Hey @Ray_Bibby
This is something I do all the time, hereโs the general setup I typically have for the following scenario:
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
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?
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โ.
For reference:
Hey @Ray_Bibby
This is something I do all the time, hereโs the general setup I typically have for the following scenario:
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?
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).
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)
By the way thank you everyone @LeventK, @MultiTech_Visions, @Steve, @Bahbus. Great info and great Community.
User | Count |
---|---|
35 | |
35 | |
28 | |
23 | |
18 |