yesterday set analysis in table

[ganadara]

hi, everyone

i'm having in problems to calculate the operating time of yesterday and tomorrow

i have three fields (YYYYMMDD, operating_time, event) and I'd like to make the following table.

bf_operating_time	YYYYMMDD	af_operating_time
operating time of 2018-12-26	2018-12-27	operating time of 2018-12-28
operating time of 2019-04-08	2019-04-09	operating time of 2019-04-10
operating time of 2019-04-29	2019-04-30	operating time of 2019-05-01
...	...	...

'event' field means whether or not an event occuered (0:not occured, 1: occured)

'YYYYMMDD' dimension

=aggr(only({<event={'1'}>} yyyymmdd, yyyymmdd)

what i want is bf_operating_time formula and af_operating_time formula

= sum( [set analysis] operating_time)

how can i make [set analysis] part? is it possible?

please help, thank you

[JordyWegman · Accepted solution]

Hi Ganadara,

Try this with making two new columns:

//Yesterday
Date(Floor(Date#(YYYYMMDD,'YYYY-MM-DD')-1,'YYYY-MM-DD')

//Tomorrow
Date(Floor(Date#(YYYYMMDD,'YYYY-MM-DD')+1,'YYYY-MM-DD')

The Date# is for interpreting your current date, floor for making a numeric value and then subtract/plus 1 and create the new date format.

Jordy

Climber