0

SELECT AND Issue

Unipro Admin

September 25, 2020 15:44

None

Follow

I've create a klip to show billable and none billable time for the current 7 days, so I'm creating an array to do this using SELECT AND, but its not returning any data, please help

 

Code

 

array(
SELECT(
SUM(@Hours),
AND(
@Billable = "true",
DATEVALUE(@Spent Date, "yyyy-M-dd") = DATEVALUE(TODAY(), "yyyy-M-dd")
)
),
SELECT(
SUM(@Hours),
AND(
@Billable = "true",
DATEVALUE(@Spent Date, "yyyy-M-dd") = DATEVALUE(DATE_ADD(TODAY(), "5", 1, "yyyy-M-dd")
)
)
),
SELECT(
SUM(@Hours),
AND(
@Billable = "true",
DATEVALUE(@Spent Date, "yyyy-M-dd") = DATEVALUE(DATE_ADD(TODAY(), "5", 2, "yyyy-M-dd")
)
)
),
SELECT(
SUM(@Hours),
AND(
@Billable = "true",
DATEVALUE(@Spent Date, "yyyy-M-dd") = DATEVALUE(DATE_ADD(TODAY(), "5", 3, "yyyy-M-dd")
)
)
),
SELECT(
SUM(@Hours),
AND(
@Billable = "true",
DATEVALUE(@Spent Date, "yyyy-M-dd") = DATEVALUE(DATE_ADD(TODAY(), "5", 4, "yyyy-M-dd")
)
)
),
SELECT(
SUM(@Hours),
AND(
@Billable = "true",
DATEVALUE(@Spent Date, "yyyy-M-dd") = DATEVALUE(DATE_ADD(TODAY(), "5", 5, "yyyy-M-dd")
)
)
),
SELECT(
SUM(@Hours),
AND(
@Billable = "true",
DATEVALUE(@Spent Date, "yyyy-M-dd") = DATEVALUE(DATE_ADD(TODAY(), "5", 6, "yyyy-M-dd")
)
)
)
)

• Facebook
• Twitter
• LinkedIn

1 comment

• 0
  
  Meggan King September 30, 2020 12:26

  Hello,

  It's hard to tell from your example, but the SELECT statement doesn't look correct. Your SUM() should go outside of the SELECT. The number of items in select must match the number of items in the condition. With the SUM() inside the SELECT the first part would be 1 item, and the condition could be multiple hours. 

  You could try

  SUM(
  SELECT(@Hours,
  AND(@Billable = "true", DATEVALUE(@Spent Date, "yyyy-M-dd") = DATEVALUE(DATE_ADD(TODAY(), "5", 6, "yyyy-M-dd"))
  )

  Check that @Hours, @Billable and @Spent Date all have the same number of items returned. 

   

  You can also do a SUMIF

  SUMIF(AND(@Billable = "true", DATEVALUE(@Spent Date, "yyyy-M-dd") = DATEVALUE(DATE_ADD(TODAY(), "5", 6, "yyyy-M-dd")), @Hours)

   

  Thanks

  Meggan