[신병훈련소] 6일차 과제 : 테이블 계산식과 세부 수준 계산식 (LOD)

1. 테이블 계산식 - 차이

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

 

1. 마크의 라인과 원을 이용해서 2019년 일 별 종가를 표현해 주세요.

• 종가를 하나는 라인, 하나는 원으로 나타내고 이중축을 이용하시면 되겠죠?
• “일”은 “연속형”으로 표현하는게 좋을 것 같네요.

 

 

2. 마크의 원은 “전일 대비 종가 등락 여부”로 색상을 표현해 봅니다. (상승은 파랑, 동일은 주황, 하락은 빨강)

1) “전일 대비 종가 등락”을 구하기 위해서는 테이블 계산식을 이용해야 할텐데요.
처음부터 계산식을 작성해서 구해내기 어려우시다면 다음과 같은 방법을 활용해 보세요.

 

    * 측정값에 있는 종가를 행 선반에 가져다 놓고, 퀵 테이블 계산을 이용해서 “차이”를 구해 보세요. 
    * 테이블 계산 범위는 전체 일자가 되어야 하니 테이블이 되어야겠죠? 
    * 계산 기준을 “전일”로 해야 이전 값을 가져와서 차이를 구할 수 있을 것 같아요.

2) 구해 놓은 테이블 계산 값을 차트에서 확인해 보세요.

       전날 보다 종가가 상승했으면 양수, 전날 보다 하락했으면 음수, 아니라면 0 값이 나타나겠죠. 그런데 첫 번째 날(2019-01-02)은 비교할 이전 값이 없으니, 그 날짜에는 테이블 계산 값이 안보일꺼에요. 화면 하단에 1 Null이 보이시나요. 이처럼 계산이 되지 않은 값이 있음을 확인할 수 있습니다.

3) 제대로 계산이 되었다면, 테이블 계산식을 이용해 구해 놓은 종가를 “데이터 창”으로 드래그 해보세요!
새로운 측정값이 생성되나요? 이름을 “전일 대비 종가 등락”으로 해주세요.
    * 어떤 식으로 계산되었는지 식을 확인해 봅시다! 
        * 측정값에 있는 “전일 대비 종가 등락”에서 마우스 오른쪽 버튼을 클릭하고 “편집”을 클릭해보세요. 
        * ZN(SUM([종가])) - LOOKUP(ZN(SUM([종가])), -1)
        * 위의 함수는 기준 일자의 종가 합계 값과, LOOKUP함수를 이용해서 현재 기준 일자의 -1일 자의 집계된 종가를 가져와서 계산을 하고 있네요. (DAY6 동영상 안내에 테이블 계산 함수를 보시면 다양한 함수를 볼 수 있어요.)
        * 즉, 기준일자 종가 합계에서 이전 일자의 합계를 뺀거죠. 결과가 왜 양수, 음수, 0 값이 나왔는지 아시겠죠? 

 

4) “전일 대비 종가 등락”을 이용해서 “등가 여부 색상”의 계산식을 만들어 보세요.

1. “코드명”을 “원 마크”의 “세부 정보”에 추가해 주세요.

2. 메뉴 > 워크시트 > 작업(동작)을 클릭해 주세요.

3. 작업(동작) 추가에서 URL로 이동을 클릭해 주세요.

4. URL 추가 작업(동작)에서 아래와 같이 해보세요.

• 이름 : 증권정보로 이동
• 작업(동작) 실행 조건 : 메뉴
• URL에 아래 주소 입력
• 위 주소에서 code= 뒤에 코드명을 넣어 주세요.
•  

  

• 확인 클릭
•  

5. 원 마크를 클릭하고 도구 설명에 있는 “증권정보로 이동”을 클릭해보세요.

 

 

 

 

 

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

 

1. 국가, 시도를 상세 정보에 넣고, 마크의 색상은 매출 합계로 나타내 주세요.

2. 매출의 “구성 비율”을 레이블로 표시해 주세요.
1) 매출을 레이블로 드래그 해 주세요
2) 퀵 테이블 계산의 “구성 비율”을 이용해보세요. 범위는 테이블로 해야 전국을 기준으로 비율이 계산 되겠죠?

• 구성비율 계산식을 더블클릭해서 살펴보면 아래와 같은 계산식을 볼 수 있습니다.
• SUM([매출]) / TOTAL(SUM([매출]))

3. 지역을 필터로 추가해주세요! (단일 값 선택만 가능하게 옵션을 조정해주세요.)

“수도권” 지역 필터를 선택해 보세요. 서울특별시의 전국 대비 매출 구성비율 22.13%의 값이 그대로 보이시나요?

왜 비율이 46.82%로 바뀌었을까요?

“구성 비율”을 구할 때 계산의 범위를 “테이블”로 지정해 줬습니다.

지역 필터를 적용하기 전에는 화면에 보이는 전체 시도에 대해 범위가 지정이 됐다면,
필터를 적용한 후에는 해당 지역에 포함된 '시도'에 대한 범위로 계산 범위가 바뀌게 됩니다.

즉, 필터 적용 전에는 서울특별시 매출 합계 / 전국 매출 합계 (TOTAL의 범위가 전국)로 구성비율이 구해졌다면,
필터 적용 후에는 서울특별시 매출 합계 / 인천,서울,경기도 매출 합계 (TOTAL의 범위가 수도권) 로 계산 되다보니
구성비율의 수치가 달라지게 된거죠.

 

 

 

 

 

 

 

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

이 시트의 시각화 세부 수준은 “국가”와 “시도”입니다. 이 뷰에서 매출의 합계 값을 구한다면 가장 낮은 시각화 세부 수준인 “시도” 기준으로 매출 합계 값이 구해지게 되겠죠.

그런데 우리의 최종 목적인 전국 기준 매출 구성 비율을 구하려면 “시도 매출의 합계 값”을 “전국 매출의 합계 값”으로 나눠야 했습니다.

즉, 우리에게 필요한 “전국 기준”의 매출 합계 값은 이 뷰의 수준에서는 절대 구할 수가 없는거죠.

이처럼 "현재 뷰의 시각화 세부 수준"과 "실제 내가 필요한 집계의 기준"이 상이할 때 사용할 수 있는 것이 “세부 수준 계산식” 입니다.

이러한 개념을 가지고, 아래와 같이 시도 기준 매출 비율과 전국 기준 매출 비율을 함께 나타내는 시각화를 나타내 봅시다!

 

 

1. FIXED 함수를 이용해 “전국 기준 매출” 계산식을 만들어 보세요.

• FIXED는 지정된 특정 차원을 기준으로 집계를 하는 방법입니다.
• 시도의 전체 합계 매출을 구하려면 그보다 높은 수준의 차원인 “국가"를 기준으로 매출의 합계 값을 구하면 되겠죠?

2. “전국 기준 구성비율” 계산식을 만들어 보세요.

• 아래와 같이 매출 합계를 이전에 구해 놓은 전국 기준 매출 합계로 나누면 되겠죠.

*애초에 뷰의 세부 수준이 “시도”이니 매출 합계는 시도의 매출 합계를 나타내는 것! 

3. 새로 만든 “전국 기준 구성비율”을 ‘레이블’에 추가해주세요. 레이블을 클릭해서 위의 결과와 같이 나타나게 해주세요.

 

 

 

 

 

 

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

과거 구매 고객 매출 기여도와 신규 유입 고객 현황

2015년에 첫 구매가 발생한 고객이 2018년도 까지 얼만큼 매출에 기여하고 있을까요? 아래 시각화를 살펴보니 2015년에 첫 구매를 발생시킨 고객이 여전히 2018년도까지 많은 매출에 기여하고 있음을 알 수가 있는데요. 반면에 매년 새로 유입되는 고객은 줄어드는 것을 볼 수가 있네요.

 

1. 주문일자를 이용해 연도를 열에 놓고, 매출은 행에 놓아주세요.

2. 위의 내용을 시각화 하기 위해 고객별 최초 구매 연도를 색상으로 표현해 봅시다.

예를 들어, 아래와 같이 데이터가 있을 때, 각 “고객별 최초 구매일”을 구하고, 고객 별 최초 구매일의 연도를 색상으로 표현하면 매출 막대의 색상은 고객의 유입 년도에 따라 구분되겠죠?

• “고객별 최초 구매일”이라는 이름으로 계산식을 만들어 주세요.

3. 새로 만든 “고객별 최초 구매일”을 색상에 놓아주세요.

 

 

 

 

+. 코호트 분석 

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

2015년 2분기에 처음 구매를 한 고객은 2분기가 지난 후 가장 많이 재구매를 했음

 

1. 최초 구매일은 이미 4번 과제에서 “고객별 최초 구매일”로 만들어 놓았습니다.

2. 최초 구매일을 활용해서 2번째 구매일 (“고객별 최초 재구매일”)을 구해볼까요?
     우리가 구해야하는 “고객 별 최초 재구매일” 에 사용되어야 하는 집계 기준과
     “시각화 세부수준 (뷰의 세부수준)”이 다르기 때문에 FIXED 세부 수준식을 사용해야 합니다.

아래 식을 이용해서 “고객별 최초 재구매일” 계산식을 만들어 주세요.

식이 의미하는 바를 가장 안쪽 식부터 차근차근 살펴볼까요?

(1) IIF 함수는 제일 첫 번째 조건이 만족하면, 2번째 값을, 아니라면 3번째 값을 적용한다는 의미입니다.
즉, “고객별 최초 구매일"이 "주문일자" 보다 작으면 주문일자를 가져오고, 아니라면 NULL 값이라는 얘기인데요.

아래 표와 같은 결과가 나오겠죠.

(2) IIF를 통해 구해진 주문일자 중 가장 최소값을 갖고 오게 되니, 고객별 두번째 구매날짜를 구할 수 있겠죠??
(위의 표에서 파란색 값을 가져오게 될꺼에요.)

3. “고객별 최초 구매일”과 “고객별 최초 재구매일”을 가지고 DATEDIFF 함수를 이용해서 ‘quarter’기준으로
재구매가 일어나기 까지의 시간 차이를 구해보세요. 계산식 이름은 “고객별 재구매 경과기간”으로 합니다.

4. “고객별 재구매 경과기간”의 결과 값은 숫자이기 때문에, 새로운 계산식이 측정값에 위치하는 것을 볼 수 있습니다.
하지만 우리는 “고객별 재구며 경과기간”을 집계 형태로 사용하는 것이 아니라 하나의 “차원”으로 사용해야 겠죠.
“고객별 재구매 경과기간” 필드를 드래그 하여 측정값에서 차원으로 이동시켜 주세요.

5. 구해 놓은 “고객별 최초 구매일”을 행 선반에 불연속형 “년”, “분기” 수준으로 놓아주세요.

6. “고객당 재구매 경과기간”을 열 선반에 놓아 주세요.

7. “고객번호”에서 마우스 오른쪽 버튼을 누른채 (Mac의 경우 Option을 누른채) 로 색상에 드래그 하고,
고유 카운트로 집계해주세요. 해당 기간에 해당하는 고객을 카운트 합니다.