Calculating Store Sales/Total State Sales in Pivot
Hi all,
In Qlik Sense I have two tables that look like the following:
Store_Sales:
STORE_ID
WEEK
STORE_STATE
SALES
1
11/1/2024
CA
204
1
11/8/2024
CA
264
1
12/13/2024
CA
290
1
12/20/2024
CA
320
2
10/4/2024
TX
52
2
11/8/2024
TX
688
2
12/20/2024
TX
5999
State_Sales:
STATE
WEEK
STATE_SALES
CA
11/1/2024
4592
CA
11/8/2024
5002
CA
12/13/2024
5688
CA
12/20/2024
6300
TX
10/4/2024
5883
TX
10/11/2024
6333
TX
10/18/2024
6800
TX
11/8/2024
7303
TX
12/20/2024
8201
NV
10/11/2024
1231
NV
12/13/2024
1011
I would like to create a Pivot Table in QlikSense that looks like the following:
STORE_ID
10/4/2024
11/1/2024
11/8/2024
12/13/2024
12/20/2024
1
(null)
4.4%
5.3%
5.1%
5.1%
2
0.9%
(null)
9.4%
(null)
9.2%
The goal of the pivot is to calculate a Store's contribution to the respective state's overall sales. So for example, STORE_ID 1's Sales/California's Total Sales for each week. In the full dataset there are multiple stores per state. I'm not sure how to write an expression to properly calculate this.
Hi, I think the easiest way is to create a relationship between both tables by state and week, it could be done by a composite key like "STORE_STATE &'_'& Date(WEEK,'YYYYMMDD') as IdStoreWeek... but just renaming the fields to have the same name and leaving the synthetic key will also be ok.
After that you can do a simple Sum(SALES)/Sum(STATE_SALES)
Hi, I think the easiest way is to create a relationship between both tables by state and week, it could be done by a composite key like "STORE_STATE &'_'& Date(WEEK,'YYYYMMDD') as IdStoreWeek... but just renaming the fields to have the same name and leaving the synthetic key will also be ok.
After that you can do a simple Sum(SALES)/Sum(STATE_SALES)
