[Tableau] Weeknum을 Date형식으로 Convert하여 일자별 추이 구하기

결과물

기간별 필터링을 적용하여 원하는 기간 기준으로 추이를 확인토록한 화면

Date 자료형은 다양한 계층 구조를 지닌다.

태블로에서는 Date형식을 다음과 같이 분류해준다.

년 ⇒ 분기 ⇒ 월 ⇒ 일

해당 방식은 동일 기간 내 데이터 자료를 다각도로 분석할 수 있도록 도와준다. 예를 들면, 분기별로 개괄적인 추이를 확인하고 특정 기간의 이슈를 월 혹은 일자별로 확인하는 식이다.

태블로의 Date형식 구조.

몇가지 추가 작업이 필요하지만, 태블로의 Date 계층 구조는 구글스프레드시트에서도 구현할 수 있다.

구글 스프레드시트에서 기간별 효율 추이 그래프를 적용한 화면

구글 스프레드시트에서 주별, 월별, 일별 필터를 적용한 화면

Spread Sheet의 디폴트 기간세트 SYSTEM1

스프레드 시트에서 제공하는 Date형식 Formula는 다음과 같다.
1. Weekday : 일자의 요일을 숫자형식으로 추출 (디폴트 일요일은 1)

2. Weeknum : 일자별 형식을 주별 형식의 데이터로 변경

• 기본적으로 일-토 단위로 1주를 묶는 System1 방식의 주 카운팅 방법이 적용된다.
• 중복된다. (2019.01.01이 속한 주와 2020.01.01이 속한 주의 Weeknum은 1로 동일하다.)
• 12월 마지막 주와 이듬해 1월 첫째주는 같은 주간에 있어도 다른 Weeknum가 적용된다.

2019.12.31은 Weeknum 53, 2020.01.01은 Weeknum 1이 적용된다.

Weeknum형을 DATE형식으로 변환하는 과정

구글 스프레드시트는 태블로와 달리, 통계로 활용하기 위해서 몇 가지 추가작업이 필요한데, 그 중 대표적인 처리가 인덱싱이다.

예를 들면, 2020.01.01이 속한 주의 통계를 확인할 때, 구글 스프레드 시트의 Weeknum 1로 불러오면, 유저 입장에서 어떤 날의 정보인지 가늠하기 어렵다. 이럴 때 Weeknum 1을 2020.01.01 ~ 2020.01.04로 인덱싱해주는 작업이 필요하다.

모든 데이터는 가장 작은 단위에서부터 통계를 추출하므로, 원시 데이터로 일별 데이터를 가지고 있다고 가정하자.

일별데이터로 구성된 원시데이터 세트. 편의상, B칼럼에 두었다.

인덱싱을 위해 유저가 식별하기 쉬운 명칭으로 일별 데이터를 가공한다.

일-토까지를 1주로 보기 때문에, 당해년도에 한해서 일별 데이터는 총 7개의 중복값을 지닌다.

ARRAYFORMULA(
IF(B3:B="","", # B칼럼 중 존재하는 값만 계산한다.
CONCAT(CONCAT(TEXT(B3:B,"YY"),"년 "), # 계산할 일자의 년도를 한글화한다.
CONCAT(WEEKNUM(B3:B,1),"주차") # 계산할 일자의 주차번호를 한글화한다.
)
)
)

A:B 칼럼의 정보를 활용해서 C 칼럼에 Vlookup한다.

칼럼명이 기간인 C칼럼에는 해당 주차의 시작일과 마감일을 보기 좋게 인덱싱할 예정이다.

한주는 7일이니, 마감일은 통상 시작일의 6일 후로 볼 수 있기에, 다음 코드를 생각할 수 있다.

ARRAYFORMULA(
IF(B3:B="","",
CONCAT(
CONCAT(
TEXT(B3:B,"YY-MM-DD(DDD)")," ~ "), #B칼럼의 시작일을 불러온다.
CONCAT(
TEXT(B3:B+6},"YY-MM-DD(DDD)"),"")) #시작일에 6일을 더한 값을 마감일로 설정한다.
))

하지만, 시작일이 무조건 일요일인 경우는 없으므로 다음의 작업을 추가해둔다.

ARRAYFORMULA(
IF(B3:B="","",
CONCAT(
CONCAT(
TEXT({VLOOKUP(A3:A,A:B,2,0)+{1-WEEKDAY(VLOOKUP(A3:A,A:B,2,0))}},"YY-MM-DD(DDD)")," ~ "), #B칼럼에서 시작일을 불러온다.
CONCAT(
TEXT({VLOOKUP(A3:A,A:B,2,0)+{7-WEEKDAY(VLOOKUP(A3:A,A:B,2,0))}},"YY-MM-DD(DDD)"),""))
#B칼럼에서 불러온 시작일 정보를 계산해서 마감일을 맞춘다. (* 7-시작일의 요일값)
))

여기서 7 - 시작일의 요일값으로 마감일을 계산한 이유는 다음과 같다.

기본적으로 일요일은 숫자 1이 할당된다.

마감일 = 시작일 + {7 - 시작일(요일값)}
1. 모든 값은 일 - 토이며, 마감일은 무조건 토요일이다.
2. 토요일(7)이 시작일인 경우, 마감일도 토요일(7)이므로 0의 값을 더한다.
3. 일요일(1)이 시작일인 경우, 마감일인 토요일(7)까지 6의 값을 더한다.
4. 수요일(4)이 시작일인 경우, 마감일인 토요일(7)까지 3의 값을 더한다.

다음과 같이 시작일을 계산하여, 마감일 정보를 추출하였다.

하지만, 여기서도 문제가 있다. 12.31이 속한 주와 01.01이 속한 주를 구별하지 못한다.

앞서 말한 바, 12월 마지막 주와 이듬해 1월 첫째주는 같은 주간에 있어도 다른 Weeknum가 적용된다. 이런 경우, Weeknum 구분 기준에 맞추어 12.31과 01.01이 같은 주에 포함되어 있어도, 분류해주어야 한다.

이를 위해서 다음의 Formula문이 적용된다.

ARRAYFORMULA(
IF(B3:B="","",
IF(WEEKNUM(B3:B)=1, # 매해 1번째 주간인 경우,
CONCAT(
CONCAT(
TEXT({VLOOKUP(A3:A,A:B,2,0)},"YY-MM-DD(DDD)")," ~ "),
CONCAT(
TEXT({VLOOKUP(A3:A,A:B,2,0)+{7-WEEKDAY(VLOOKUP(A3:A,A:B,2,0))}},"YY-MM-DD(DDD)"),"")),# 시작일은 1월 1일로 리턴한다. 마감일은 1월 1일 시작일을 기준으로 계산한다.
IF(WEEKNUM(B3:B)=53, # 매해 53번째 주간인 경우, 53번째 주간은 마지막 주간이다.
CONCAT(
CONCAT(
TEXT({VLOOKUP(A3:A,A:B,2,0)+{1-WEEKDAY(VLOOKUP(A3:A,A:B,2,0))}},"YY-MM-DD(DDD)")," ~ "),
CONCAT(
TEXT({VLOOKUP(A3:A,A:B,2,0)+
{COUNTIF(A:A,CONCAT(CONCAT(TEXT(B3:B,"YY"),"년 "),CONCAT(WEEKNUM(B3:B,1),"주차")))-1}},"YY-MM-DD(DDD)"),"")),# 시작일 이후 12월 31일까지 남은 일수를 시작일과 더해 마감일을 구한다.
CONCAT(
CONCAT(
TEXT({VLOOKUP(A3:A,A:B,2,0)+{1-WEEKDAY(VLOOKUP(A3:A,A:B,2,0))}},"YY-MM-DD(DDD)")," ~ "),
CONCAT(
TEXT({VLOOKUP(A3:A,A:B,2,0)+{7-WEEKDAY(VLOOKUP(A3:A,A:B,2,0))}},"YY-MM-DD(DDD)"),""))
))))

Weeknum와 기간 인덱싱이 정확히 매핑되었다.

태블로로 넘기면 주별 넘버를 통해 기간 정보가 정확히 인덱싱된 모습을 확인할 수 있다.