날짜, 요일별 판매 트렌드 모니터링
1. Dateset 살펴보기
-
데이터를 살펴보면 1월과 2월을 기준으로 Store의 개수가 확 줄어든다
-
이부분을 좀 더 살펴보기 위해 행렬차트를 생성해본다
-
지금 1로 시작하는 가게들만 2월 판매데이터가 있고 나머지는 없음
-
누락되어 있을 수도 있고 나머지 가게가 폐업을 했을 수도 있음
-
현업상황이라면 관계자와 상의 후 데이터를 어떻게 할지 결정
-
우리는 우선 값이 있는 가게들만 필터링해서 보겠음
-
중복되는 가게의 값들만 살펴보는 방법 (2가지)
-
슬라이서를 생성해서 중복되는 가게들을 선택
(BUT 대시보드를 보는 다른 누군가가 슬라이서 설정을 바꿔버릴수 있음) -
필터사용하기
- 이 페이지의 필터
- 여백을 누른 상태에서 필터페이지 열기
- 필터 잠그기 (다른 사람이 못건들임) / 자물쇠모양
- 필터 숨기기 (작성한 사람만 볼 수 있음) / 눈모양
-
2. 분해트리
- 데이터를 자동으로 집계하며 임의의 순서로 차원을 드릴다운할 수 있음
-
우리는 날짜별로 판매량이 어떻게 되는 지 분해트리를 통해 시각적으로 표현해보겠음
-
분석에 분석할 값을 넣어주고 설명기준에 날짜를 넣는데 분기는 필요 없으니 없애줌
-
이렇게 처음에는 드릴다운 전 수량의 합계가 나옴
-
높은 값 / 낮은 값을 선택해서 드릴다운 할 수 있음
-
높은값을 선택해서 보겠음
-
우리가 가진 날짜는 년도가 하나여서 하나의 분해트리로 나눠져 나옴
-
또 +를 누르면 월 - 일 순서대로 높은 값을 기준으로 드릴다운 됨
-
1월과 2월 중엔 1월이 1월안에서는 29일이 가장 판매량이 높음
-
현재 크기는 각 드릴다운 단위(년-월-일)안에서 최대값을 기준으로 되어있음
- 최상위 노드로 변경하면 전체 판매량을 기준으로 막대가 참
3. 판매가격 & 매입가격
- 판매할때의 단가와 물건을 가져왔을 때의 단가를 비교하여 실제 이익이 얼마나 남았는가를 확인하고 싶음
방법
1. Purchase Price(단가) 가 들어있는 새로운 Dimension Table을 만듬
→ Sales Table과 Join
2. Sales Table에 Purchase Price 새로운 컬럼을 추가 (left join)
-
첫번째 방법을 먼저 시도해보자
-
테이블들은 살펴보면 단가가 있는 테이블은 2가지
- Purchases테이블의 Purchase Price컬럼
- Inventory테이블의 Price컬럼
-
Inventory 테이블을 이용해 새로운 Demension테이블을 만들어 보겠음
-
데이터변환탭으로 들어가 BegInv와 EndInv로 만들었던 Inventory 테이블을 복제해줌
-
참조는 컬럼을 변경할 필요없이 테이블 자체를 가져오고 싶을때 사용
-
Ctrl을 누른채로 필요한 컬럼만 선택 → 우클릭 →
다른 열제거
-
우리는 Key값으로 InventoryId를 사용할 예정
-
전에 Null값으로 있던 City값에 도시이름을 찾아 넣어주었는데 이 값들은
InventoryId에 city가 빠져있음 -
이를 해결하기 위해 Store, City, Brand를 사용해서 InventoryId를 설정해주겠음
-
컬럼을 합쳐주려면 컬럼타입이 동일해야 함
-
Store, Brand 모두 텍스트로 변경
- 열추가 → 사용자지정열
-
새 테이블의 이름은 D_Inventory_Price
-
이 상태로 두테이블을 연결해주려고 하면 다대다 오류가 생김
-
Inventory ID별 Price가 하나씩 존재해야하는데 그렇지 않아서 다대다로 연결이 된다
-
BegInv와 EndInv을 합쳐서 만들었기 때문에 같은 ID로 값의 수가 2개가 있을 것임
-
그럼 우리는 고유가격이 되도록 줄여줘야 함
-
기준을 보수적으로 더 비싼 값 매입가격이 남도록 하자
-
이럴때 사용할 수 있는것 = 변환탭 → 그룹화
- Inventory 아이디별로 가격을 그룹화했을 때 더 가격이 높은 값을 남겨 새로운 열을 만들겠다는 것임
- 그리고 나서 확인해보면 InventoryId의 고유값과 행의 개수가 같은걸 확인할 수 있음
-
이제 일대다로 두 테이블을 연결할 수 있음
-
두개의 테이블이 연결되었기 때문에 우리는 이제 Sales테이블을 이용할 때 단가값을 D_Inventory_Price에서 참조할 수 있음
-
판매가격과 매입가격을 확인해보기 위해 테이블을 만들었는데 문제가 있다
-
우리의 재고 데이터는 2016-1-1과 2016-12-31 두 날짜의 스냅샷만 찍혀있음
-
때문에 12/31이 되기전 재고가 없어졌을 경우, 매입단가가 찍혀있지 않을 수 있음
-
공란은 평균값이나 다른 값을 참조해서 채워 줄 수도 있음
-
우리는 판매가격과 매입가격의 차이를 보려고 하는것이니 그 차이가 그냥 0이 될 수 있도록 판매가격을 매입가격으로 채워넣어주겠다
-
이때 만들어둔 D_Inventory_Price를 사용해 두번째 방법을 사용하겠음
(Sales Table에 Purchase Price 새로운 컬럼을 추가 (left join)) -
데이터변환탭 선택
- D_Inventory_Price를 클릭후 쿼리병합 선택
-
병합되는 기준열(InventoryId)을 선택
-
만약 Key값이 여러개라면 Ctrl키 누른채로 다중선택이 가능하다
순서대로 Sql과 비교해보면
Left Join - Right Join - OuterJoin - Inner Join
앤티는 anit를 나타냄 - 왼쪽 / 오른쪽 존재하지 않는 경우만 출력함
-
그러면 Sales테이블에 이렇게 D_Inventory 값이 붙는다
-
상단의 맨 오른쪽 아이콘을 선택
-
단가만 붙여줄 것이므로 PurchasePrice만 선택
-
원래 열 이름을 접두사로 사용 하면
D_Inventory_Price.PurchasePrice가 열이름으로 붙는다 -
이 경우 첫번째 방법과 동일하게 빈값이 발견된다
-
이를 판매가격으로 채워주기 위해 CQALESCE함수를 사용해 새열을 추가해준다
- 해석해보면 PurchasePrice값으로 채우고 그것이 빈값이면 SalesPrice를 채워라
- 이러면 이렇게 빈값이 있던 곳이 SalesPrice로 채워짐
- 새로 만들어진 PuchasePrice_F를 사용해 판매이익을 계산해볼 수 있다
- 매입가 대비 판매이익율을 보여주는 식을 작성해줄수 있음
- 역마진을 나타내는 구간도 보임
4. Calender Table 생성
-
요일별로 판매량, 판매액을 확인해보고 싶어졌다
-
요일별로 분류해줄 수 있게 캘린더 테이블을 새로 생성해보자
-
CALENDAR(<start_date>, <end_date>) -
시작날짜와 끝날짜를 넣어주면 그 기간의 날짜값이 생김
- SalesDate의 첫날과 마지막날짜로 이루어진 D_Calendar 테이블을 생성해준다
-
위에 연도를 나타내는 컬럼은
Year(D_Calendar[Date])으로도 동일하 결과 출력 -
Month / Day도 동일
-
Weeknum은 요일을 숫자로 출력해주는 함수 인데
WEEKNUM(<date>[, <return_type>])-- return_type : 1 - 일요일시작 -
요일이름으로 나타내주고 싶어서 if문으로 컬럼을 생성해주겠다
- 만들어준 D_Calendar 테이블을 Date컬럼으로 Sales 테이블과 연결해준다
-
연결해주고 나면 D_Calendar가 1에 해당하고 Sales의 SalesDate가 다에 해당하기 때문에 트리맵의 설명기준이 줄어들어있음 (D_Calendar의 영향)
-
필드를 D_Calendar의 Date로 변경해주면 된다
5. 요일별 판매트렌드
- 새로 만들어준 D_Calendar의 Date로 수량을 확인해보니 요일별로 패턴을 나타냄
-
금요일에 피크를 찍고 평일에 하락
-
날짜별 판매수량을 나타내줄 새측정값 작성
-
그냥 SalesQuantity를 날짜별로 합산해준것과 수량의 합을 날짜로 나눠준 측정값은 합계에서 차이를 보임
-
Daily AVG Sales Qty는 합계도 전체일 평균값으로 나옴
- 일평균 판매액 과 일평균 단가도 구해준다
- 요일별 트렌드를 나타내주는 그래프도 만들어 줄 수 있다
- 보조축의 최소값을 조절해주면 막대와 선이 겹치는 부분을 조절해줄 수 있다
6. 계기차트
-
목표 진행률을 표시
-
KPI와 같은 백분위수 측정값을 표시
-
단일 측정값의 상태를 표시
-
정보를 쉽게 검색하고 이해할 수 있도록 표시
-
누적된 양이 목표를 얼마나 달성했나를 보여줌
-
대상값은 임의로 10000으로 정해두겠음
Sales Aim = 10000
7. KPI 생성
-
요일별 판매량의 평균값을 목표값으로 설정해서 해당 요일에 요일 목표치를 초과달성했는지 미달했는지 확인할 수 있는 KPI지표를 생성하려함
-
그를 위해 대상(목표값)에 넣어줄 새측정값을 작성해야 한다
CALCULATE :
주어진 식을 특정 필터 컨텍스트에서 계산하는 함수입니다.
AVERAGEX :
테이블의 각 행에 대해 표현식을 계산하고, 그 결과의 평균을 반환합니다.
여기서 VALUES(D_Calendar[Date]) 테이블의 각 날짜에 대해 [S1_Daily AVG Sales Qty]를 계산하고 평균을 구합니다.
(Values를 사용하면 중복값을 제거한 테이블을 표시해줌)
ALL(Sales[SalesDate])와 ALL(D_Calendar[Date]):
Sales 테이블의 SalesDate 컬럼과 D_Calendar 테이블의 Date 컬럼에서 모든 필터를 제거합니다.
필터가 제거된 상태에서 이후의 필터 조건을 다시 적용하기 위함입니다.
FILTER(D_Calendar,D_Calendar[Weeknum]=WEEKDAY(MAX(Sales[SalesDate]))):
D_Calendar 테이블에서 현재 컨텍스트의 최대 Sales[SalesDate] 날짜의 요일(Weekday)과 동일한 주 번호(Weeknum)를 가지는 행들만 포함하도록 필터를 적용합니다.
MAX(Sales[SalesDate])는 현재 컨텍스트에서의 가장 최근 판매 날짜를 의미합니다.
FILTER(D_Calendar, D_Calendar[Date] < MAX(Sales[SalesDate])):
D_Calendar 테이블에서 현재 컨텍스트의 최대 Sales[SalesDate] 날짜보다 이전 날짜들만 포함하도록 필터를 적용합니다.
요약
이 식은 Sales 테이블의 판매 날짜에 대한 특정 날짜(MAX(Sales[SalesDate]))를 기준으로 다음을 수행합니다:
식의 순서
1. 모든 필터를 제거하고 D_Calendar 테이블과 Sales 테이블의 날짜 필터를 초기화
2. 현재 최대 판매 날짜와 같은 요일을 가지는 D_Calendar 테이블의 행을 필터링
3. 최대 판매 날짜 이전의 D_Calendar 테이블의 행을 필터링
4. 이러한 필터 조건이 적용된 상태에서, 각 날짜별로 [S1_Daily AVG Sales Qty]의 평균을 계산합니다.
즉, 이 식은 현재 판매 날짜 이전의 동일한 요일에 대한 평균 판매 수량을 계산하는 식입니다.
