topic Error while using only function in expression editor in App Development

Error while using only function in expression editor

ChethanKumar — Thu, 28 Oct 2021 09:30:05 GMT

Hi,

I want to list all the ids within the range of start date and end date, but am getting error like "Invalid Dimension" if I use the below query in expression editor. Here vStartDate and vEndDate are input variables. Whats wrong am doing here

only({(<[Start Date] = {">=$(vStartDate)"}> * <[Start Date] = {"<=$(vEndDate)"}>)
+ (<[End Date] = {">=$(vStartDate)"}> * <[End Date] = {"<=$(vEndDate)"}>)
+ (<[Start Date] = {">=$(vStartDate)"}> * <[End Date] = {"<=$(vEndDate)"}>)
}ID)

Re: Error while using only function in expression editor

Or — Thu, 28 Oct 2021 09:43:10 GMT

This formula should be used in an expression, not a dimension, in which case it will work correctly. If you need it to be a dimension, you'll have to wrap it in an aggr() for a context in which to evaluate it.

aggr(only({(<[Start Date] = {">=$(vStartDate)"}> * <[Start Date] = {"<=$(vEndDate)"}>)
+ (<[End Date] = {">=$(vStartDate)"}> * <[End Date] = {"<=$(vEndDate)"}>)
+ (<[Start Date] = {">=$(vStartDate)"}> * <[End Date] = {"<=$(vEndDate)"}>)
}ID),ID)

Re: Error while using only function in expression editor

ChethanKumar — Thu, 28 Oct 2021 12:54:07 GMT

Thank you, but the data is not within the given date range. Any idea

Re: Error while using only function in expression editor

Or — Thu, 28 Oct 2021 13:08:21 GMT

I don't have your data, so I can't test it. It worked fine with sample data I generated...

Re: Error while using only function in expression editor

ChethanKumar — Thu, 28 Oct 2021 13:42:46 GMT

Here each ID can have different set of start date , end date and ProjectID. I need to list only the IDs , start date and end date, within any of the given range of start date (1-10-2021) to end date (31-10-2021). For example startDate > 1-10-2021 or endDate < 31-10-2021 or in between startDate > 1-10-2021 and endDate < 31-10-2021

Any suggestion

Am getting this below data list

ID	Start Date	End Date	ProjectID
1	01-07-2020	31-12-2022	P1
1	01-07-2021	31-08-2021	P2
2	01-07-2021	31-12-2021	P3
2	02-08-2021	07-08-2021	P4
3	01-05-2021	31-12-2021	P5
3	01-08-2021	31-08-2021	P6
4	31-03-2021	31-12-9999	P7
4	01-08-2021	31-08-2021	P8
5	01-02-2021	30-09-2021	P9
5	01-06-2021	31-12-2021	P10
5	02-08-2021	07-08-2021	P11