[태블로 신병훈련소] 6일차 학습 : 고급계산식

 

Tableau 고급 계산 

Data : 슈퍼 스토어 

 

 

배송시간 : XX 시간 YY분으로 표현하기 

 

슈퍼 스토어 데이터에는 날짜 정보(주문 일자, 배송 일자)가 있다. 

고객이 주문을 해서 언제 배송이 되었는지 정보를 바탕으로 고객이 주문 후 배송을 받는 시점까지의 시간을 알아보고자 한다.

 

 

첫번째로 각각의 주문 정보에 대해 주문 일자와 배송 일자를 알아보기 위해 주문 번호와 날짜 정보를 '년-월-일(YMD)'형태로 가져왔다. 주문 일자와 배송일자를 대조하면 배송기간을 파악할 수 있지만, 일일이 대조하는 것은 매우 비효율적이다. 따라서 DATEDIFF 함수를 사용하여 배송 기간을 계산하는 새로운 필드를 생성한다. 

 

 

생성된 필드를 열에 올리고, 불연속형으로 변경하면 배송 기간을 분단위로 계산된 열을 확인할 수 있다. 

 

 

 

배송 기간을 시간 단위로 알아보기 위해 분 단위로 계산된 필드를 60으로 나눈 후 정수로 나타나도록 계산식을 작성하였다. 

 

 

생성한 필드를 바탕으로 배송 시간을 XX시간 YY분 형태로 나타내기 위해 다음과 같은 과정을 처리하였다.

 

배송 기간을 분으로 나타낸 필드에서, 시간으로 계산한 정수형 필드에 60을 곱한 값을 빼준다. 이렇게 하면 시간으로 계산한 배송 기간을 정수형으로 바꾸지 않았을 때의 소수점 이하 값, 즉 남은 분이 계산된다.

 

XX시간 YY분 형태로 나타내기 위해 문자열 함수를 사용하여 계산식을 작성한다.

 

완성한 계산식을 열에 올려놓으면 아래와 같이 원했던 형태의 배송 시간으로 나타남을 확인할 수 있다. 

 

 

데이터를 보면 배송기간 (남은 분)이 모두 0으로 나타나는 것을 볼 수 있는데, 이는 데이터 자체가 '년-월-일'까지만 포함이 되어있고, 시간과 관련한 데이터를 포함하지 않기 때문이다. 따라서 시간으로 계산한 배송기간과 분으로 계산한 배송기간이 동일하기 때문에 0분으로만 표현이 된다.  

 

 

배송 시간 - 지도 표현

 

 

위에서 계산한 배송 기간을 지도위에 표현하고자 한다.

매출의 합계를 크기로, 평균 배송기간 (시간)을 색상으로 위와 같이 표현하였다. 

 

 

세부 수준 계산식 (LoD : Level of Detail Expression)

태블로의 데이터를 불러오면 측정값과 차원값으로 나뉜다.

 

 

측정값은 값을 집계한다는 특징이 있다. 따라서 측정값을 열에 올려놓았을 때 모든 행들이 집계되어 합계, 평균 등으로 나타나는 것을 확인할 수 있다. 

(ex. 합계(매출), 합계(수익))

 

 

측정값에 차원값을 추가하게 되면, 차원 별 측정값 집계가 된다. 

(ex. 제품 대분류 별 합계(매출), 제품 중분류 별 합계(수익))

 

 

위와 같은 경우는 제품 중분류 별 매출을 나타내는 시각화이다. 이때 제품 대분류별 전체 매출 합계도 함께 보고 싶지만, 대분류별 매출은 이미 화면에 추가된 세부 수준과 다르게 표시된다. 이러한 경우 우리는 세부 수준 계산식을 사용할 수 있다. 

 

 

{ [ INCLUDE | EXCLUDE | FIXED ] [차원] ... : 집계식( [ 측정값 ] ) }

 

화면상의 세부 수준(LoD)와 관계 없이 계산이 필요한 경우에 사용한다. 

• 고객 당, 주문 당 등 '~당' 계산이 필요한 경우
• 집계를 다시 집계해야 하는 상황
• 집계 결과를 기준으로 구간을 나눠야 하는 경우
• 뷰에서 보고 있는 수준 보다 아래 또는 위 수준에서 계산 결과를 만들어야 하는 경우 

 

구성 요소

 

 

시각화(뷰)의 세부 수준을 결정하는 구성 요소는 위 그림의 빨간색으로 표시된 모든 차원을 의미한다. 즉, 차원을 빨간색으로 표현된 곳에 놓으면 그 수준으로 시각화의 세부 수준이 나뉘게 되는 것이다. 

 

 

예를 들어 제품 중분류라는 차원을 색상에 추가하면 색상으로 제품 중분류의 수준이 나뉘는 것을 확인할 수 있다. 각각의 색상은 제품 중분류를 나타내고, 각 색상의 크기는 제품 중분류 별 매출을 나타낸다.

 

 

---

 

FIXED

 

위는 FIXED함수를 사용하여 제품 대분류의 합계를 나타낸 것이다. 제품 대분류, 중분류 별로 매출의 합계가 나뉘었지만, 오른쪽 열을 보면 제품 중분류와 상관없이 제품 대분류의 합계가 중복해서 나타나는 것을 확인할 수 있다.

 

계산된 필드에서 사용된 FIXED함수는 제품 중분류 차원이 뷰에 추가되어 있지만, 그것을 고려하지 않고 제품 대분류 수준으로 고정한다는 의미로 해석할 수 있다. 이것은 EXCLUDE함수를 사용하여 중분류를 제외시켜도 동일한 값을 나타낸다. 

 

 

EXCLUDE

 

EXCLUDE 함수를 적용한 결과도 FIXED함수를 적용한 결과와 같은 것을 확인할 수 있다. 

 

 

INCLUDE

 

 

주문번호 별 매출의 합계와 INCLUDE함수를 사용하여 지역이라는 수준을 포함하여 매출을 나타내는 필드를 생성한 결과이다. 

지역 수준이 포함되었지만 각 열에 나타나는 값의 차이가 없어보인다. 매출액의 집계를 합계가 아닌 평균으로 변경해보자. 

 

 

그 결과, 매출액의 평균은 변경 되었지만 INCLUDE를 사용한 매출액의 평균은 변화가 없었다. 

 

 

그 이유는 무엇일까? 

 

계산식에서 고려했던 지역 차원을 추가하였다. 각각의 주문번호에 매칭되는 지역이 하나임을 파악할 수 있었다.

추가적으로 제품 코드 차원을 추가해보았다. 주문 번호 하나에 제품 코드가 여러개가 매칭이 된다. 

 

전체 합계의 평균을 낸다는 것은 가장 세부 수준인 제품 코드별 매출액을 모두 더한 후, 제품 코드의 수로 나눈 값을 의미한다. 반면, INCLUDE 계산식은 이미 뷰에 추가된 차원에 내가 계산식에 추가한 차원만을 고려하여 평균을 계산한다. 따라서 제품 코드를 뷰에 추가하기 전에는, 전체 합계는 가장 세부 수준인 지역 차원 1로 나눈 값과 동일하게 나올 수밖에 없다.

 

---

 

 

유입 시점별 고객 매출 기여도

 

연도 별 매출액을 나타낸 시각화이다. 이때, "2014년도에 처음 구매한 고객이 2017년도에도 주문을 했는가?", "주문을 했다면 2017년도의 매출에 얼마만큼의 비중을 차지하고 있는가? "를 알아보고자 한다.

 

 

 

FIXED함수를 사용하여 고객 별 최초 주문일자를 나타낼 수 있는 필드를 생성한 후 색상으로 표현하였다.

 

 

 

구성 비율로 보고 싶다면 퀵 테이블 계산 - 구성 비율 을 선택한다.

이를 각각의 연도별로 보고 싶은 경우 계산 대상 - 테이블(아래로)를 선택한다. 

 

결과적으로, 2017년도의 매출액에서 2014년도에 첫 구매를 한 고객이 차지하는 매출 기여도는 73.62%가 된다. 

 

 

주문번호 별 평균 매출 금액

 

 

주문번호 당 평균 매출 금액을 알아보기 위해 주문번호를 행에, 매출을 열에 두었다.

 

추가적으로 레코드 수를 열에 올려놓았을 때, 주문번호가 갖고 있는 레코드 수가 1이 아닌 것을 확인할 수 있다. 즉, 주문번호를 포함하고 있는 행의 숫자가 6개가 있다는 것이다. 이는 동일한 주문번호를 가지고 여러 제품을 주문한 경우에 발생한다. 

 

따라서 단순히 매출의 집계를 평균으로 변경하는 것은 전체 합계를 레코드의 수로 나눈 값이 된다. 

 

분석 탭의 평균라인을 테이블로 그려본다면 다음과 같이 평균 수치를 알 수 있다. 이는 주문번호라는 기준이 이미 화면에 포함되어 있는 경우이다. 

 

 

주문번호 당 평균 매출 금액 - LoD

 

주문번호 당 평균 금액을 숫자로 표현하고 싶은 경우, 차원을 기준으로 집계가 필요하기 때문에 LoD 계산식을 사용한다. 

 

 

INCLUDE 함수와 FIXED함수를 사용하여 계산식을 생성한 후, 텍스트에 올려놓는다. 그 후, 집계를 모두 평균으로 바꾼다. 

단순 매출을 평균으로 한 것과 값의 차이가 존재함을 알 수 있다. 

 

 

테이블 계산

 

4일차 학습 참고 

 

테이블 계산은 기존 집계 계산의 결과 집합 위에 추가 계산을 실행하는 것이다. 

 

 

정렬

 

 

지역별 제품 중분류 별 매출을 내림차순으로 정렬한 결과이다. 위 그래프를 보면 가장 세부 항목인 제품 중분류의 매출 내림차순이 제대로 반영되지 않음을 알 수 있다. 

 

 

지역과 제품 중분류 모두를 고려한 매출의 정렬을 테이블 계산을 통해 해보자. 

 

 

매출을 세부정보에 놓은 후 퀵테이블 계산 - 순위 를 선택하고 불연속형으로 만들어준다. 그 후, 지역과 제품 중분류 필드 사이에 놓아 매출의 순위를 볼 수 있도록 하였다. 

 

 

각 지역 별 순위를 알아보기 위해 계산 대상 - 패널(아래로)를 선택하면, 각 수도권 별 제품 중분류의 매출 순위가 나타난다. 

 

 

이 계산 필드를 필터로 놓아 순위를  1~3위만 표시하도록 하고 뷰에서 표시를 제거하고, 지역을 색상으로 구분한다.

 

그 결과, 지역별 Top3(매출 기준) 제품을 나타내는 시각화가 만들어진다. 

 

 

 

기록 수익

 

 

각 분기별 수익이 최대를 찍을 때마다 기록 수익인가를 확인하고자 한다. 위 시각화에서는 각 분기별 수익이 최고치를 찍을 때 이전 최고 기록 수익과 비교가 어렵다. 

 

 

분기의 수익 누계값 중 최대를 알기 위해 RUNNING MAX 함수를 사용하여 필드를 생성한다. 그 후, 행을 복제하고 생성한 필드를 레이블에 올려준다. 

 

 

값들이 기록 수익인가를 알아보기 위해 수익이 현재 가장 높은 수익과 동일한지의 여부를 판단하는 새로운 필드를 생성한다. 이 필드를 색상에 올려준 후, 행에 있는 필드를 이중축, 축 동기화를 하여 겹쳐지게 하였다. 

 

기록 수익인 경우는 주황색, 기록 수익이 아닌 경우는 파란색으로 표시되어 있다. 

 

 

---

6일차 과제

 

1. 테이블 계산식 - 차이

 

Data : 주식 Data

 

주식데이터를 가지고 전일 대비 종가가 상승했는지 하락했는지 나타내는 차트 그리기

 

 

• 전일 대비 종가 등락을 구하기 위해 테이블 계산식을 사용한다. 
  • 측정값에 있는 종가를 행 선반에 가져다 놓고, 퀵 테이블 계산 - 차이를 선택한다. 
  • 테이블 계산 범위는 전체 일자가 되도록 테이블로 설정한다.
  • 계산 기준을 이전으로 설정한다. 

 

 

• 전날 보다 종가가 상승한 경우 양수값을, 하락한 경우 음수값을, 아니라면 0값을 나타낸다. 
• 2019-01-02는 비교할 이전 값이 없어 테이블 계산값이 보이지 않고, 오른쪽 하단의 1 null이 나타난다. 

 

 

• 테이블 계산식을 "전일 대비 종가 등락"이라는 이름으로 새로운 계산 필드로 생성한다. 
  • 계산식을 적는 공간에 필드를 드래그 하면 " ZN(SUM([종가])) - LOOKUP(ZN(SUM([종가])), -1 " 이라는 계산식이 생성됨을 확인할 수 있다.  
• "전일 대비 종가 등락" 필드를 활용하여 "등가 여부 색상" 계산식을 생성한다. 

 

 

• 합계(종가)를 행에서 복제한 후, 복제한 필드의 마크를 원으로 변경한 후, 이중축, 축동기화를 설정한다. 

 

 

• 원 마크를 "등가 여부 색상"필드를 통해 색상으로 구분한다.
• 종가를 레이블에 올리고, 최대/최소값만 나타내도록 설정한다.  

 

[추가] 워크시트 작업(동작) 활용하기 

 

• 코드명을 원 마크의 세부정보에 추가한다.
• 메뉴 - 워크시트 - 동작을 선택한다.
• 동작 추가에서 URL로 이동을 선택한다. 
• URL 추가 동작을 다음과 같이 작성한다.
  • 이름: 증권정보로 이동
  • 동작 실행 조건 : 메뉴
  • URL에 주소 입력: https://finance.naver.com/item/main.nhn?code=
  • 삽입을 눌러 코드명을 넣는다.
  • 확인 클릭

 

• 원 마크를 클릭하면 세부정보에 증권정보로 이동 하이퍼링크가 생성된 것을 확인할 수 있다.

 

2. 테이블 계산식 - 구성비율

 

Data : 주문+반품 추출

 

 

• 국가, 시도를 상세 정보에 넣고, 마크의 색상을 합계(매출)로 나타낸다.

 

 

• 매출을 레이블로 드래그 하고, 퀵 테이블 계산 - 구성 비율을 선택한다. (범위 - 테이블)
• 구성 비율 계산식 = SUM([매출]) / TOTAL(SUM([매출]))
  • TOTAL함수는 테이블 계산 함수

 

 

• 지역을 필터로 추가하고, 수도권 지역 필터를 선택한다.

---

• 지역으로 필터링 하기 전 서울의 전국 대비 매출 구성비율은 22.13%이다. 
• 지역으로 필터링 후 서울의 매출 구성비율은 46.82%이다.
• 지역 필터를 적용하기 전에는 화면에 보이는 전체 시도에 대해 범위가 지정된 것이고, 필터를 적용한 후에는 해당 지역에 포함된 시도에 대한 범위로 계산 범위가 바뀌게 된 것이다. 
• 즉, 필터 적용 전에는 서울특별시 매출 합계 / 전국 매출 합계 (TOTAL 범위 : 전국), 필터 적용 후에는 서울 특별시 매출 합계 / 인천, 서울 경기도 매출 합계 (TOAL 범위 : 수도권)으로 계산되기 때문에 구성비율의 수치가 달라지게 된다. 

---

 

 

3. 세부 수준 계산식 (LoD) : 전국 기준 매출 구성비율 구하기

 

Data : 주문+반품 추출

 

세부 수준 계산식을 이용해 지역 기준 / 전국 기준 매출 구성비 살펴보기 

 

 

세부 수준 계산식
현재 뷰의 시각화 세부 수준과 실제 내가 필요한 집계의 기준이 상이할 때 사용할 수 있는 계산식 

 

 

• FIXED 함수를 이용해 전국 기준 매출 계산식을 생성한다.
  • 시도의 전체 합계 매출을 구하기 위해 그보다 높은 수준의 차원인 국가를 기준으로 매출의 합계를 구한다. 
• 전국 기준 구성비율 계산식을 생성한다. 
  • 뷰의 세부 수준이 시도이기 때문에 매출 합계는 시도의 매출 합계를 나타낸다. 

 

 

• 새로 생성한 전국 기준 구성비율을 레이블에 추가한다.
• 결과적으로 필터를 바꿔도 전국 기준 매출 구성비율 값이 변화하지 않는 것을 확인할 수 있다. 

 

 

4. 세부 수준 계산식 : 과거 고객 매출 기여도 및 신규 유입 고객 현황 분석 

 

Data : 주문+반품 추출

 

 

• 주문일자를 이용해 연도를 열에, 매출은 행에 놓는다. 

 

 

• 위와 같이 데이터가 있을 때, 각 고객별 최초 구매일을 구하고 고객별 최초 구매일의 연도를 색상으로 표현하면 매출의 막대 색상은 고객의 유입 년도에 따라 구분될 것이다.
• 위 내용을 바탕으로 고객 최초 구매일이라는 새로운 계산식을 생성한다. 
  • 현재 시각화 세부 수준: 연도 
  • 고객을 기준으로 최초 주문 일자를 구하기 위해 FIXED함수 사용 

 

'

• 고객별 최초 구매일 필드를 색상에 놓는다.

 

 

[추가 도전] 코호트 분석 

 

Data : 주문+반품 추출

 

최초 구매가 일어난 후, 두번째 구매가 발생하기까지의 기간이 어느정도 되는지 시각화

코호트 분석
- 특정 기간 동안 공통된 특성이나 경험을 갖는 사용자 집단을 의미한다.
- 첫방문, 캠페인 유입 등 특정 조건에 해당되는 사용자들을 그룹화하고 시간 흐름에 따른 행동 패턴을 추적한다. 

 

 

 

• 최초 구매일을 활용하여 고객별 최초 재구매일 계산식을 생성한다. 
  • IIF함수는 제일 첫번째 조건이 만족하면, 2번째 값을, 아니면 3번째 값을 적용한다. 
  • 즉, 고개별 최초 구매일이 주문일자보다 작으면 주문일자를, 아니면 NULL값이다. 

 

 

• DATEDIFF 함수를 이용해 quarter기준으로 재구매가 일어나기까지의 시간차이를 구하는 계산식을 생성한다. 
• 해당 결과는 숫자이기 때문에, 측정값에 위치하는데, 우리는 집계 형태가 아닌 하나의 차원으로 사용해야하므로 필드를 드래그하여 차원으로 이동시킨다. 
• 고객별 최초 구매일을 행에 년, 분기 수준으로 놓고, 고객당 재구매 경과기관을 열선반에 놓는다.
• 고객번호를 색상으로 드래그 하고 고유 카운트로 집계한다. 

 

 

• 시각화 결과, 2015년 2분기에 처음 구매를 한 고객은 2분기가 지난 후 가장 많이 재구매했음을 파악할 수 있다.