Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
ScottS28
Partner - Contributor II
Partner - Contributor II

Comparison to different baseline values based on dimensions

I have a dataset that represents a collection of stores and their sales performance. The stores are geographically arranged into districts. We expect that districts will correlate to sales performance; districts containing large cities will have more sales than districts with no cities.

However the stores also have varying merchandising types. We expect that merchandising type will also correlate to sales performance; a store with [Merchandising Type] = 'Modern' should have higher sales than [Merchandising Type] = 'Legacy'

We want to see how a store's performance compares to average store performance within the district but also the average performance as compared to other similar Merchandising Types within the district.

We have the following data:

DistrictStoreMerchandising TypeSales Revenue
A1Modern5000
A2Modern3000
A3Legacy1000
A4Modern1000
B1Legacy2000
B2Legacy3000
B3Modern5000
C1Modern500
C2Legacy1500
C3Modern1000
C4Legacy1000

 

What I would like to display is the % above average revenue for each record within its district as well as within its district and merchandising type. In other words, I want to display the following 4 additional fields:

DistrictStoreMerchandising TypeSales RevenueDistrict AverageDistrict Merchandising Average% Above District Average% Above District Merchandising Average
A1Modern500025003000100%67%
A2Modern30002500300020%0%
A3Legacy100025001000-60%0%
A4Modern100025003000-60%-67%
B1Legacy20003333.332500-40%-20%
B2Legacy30003333.332500-9%20%
B3Modern50003333.33500050%0%
C1Modern5001000750-50%-33%
C2Legacy15001000125050%20%
C3Modern100010007500%33%
C4Legacy1000100012500%-20%


I would like the values of these fields to be fixed regardless of selections, so that if I am looking only at the record representing Store A3 (District A store 3), I can see the comparison to baseline values within the district and merchandising type. Otherwise I will always only see that Store A3 is performing 0% above both averages (since the datasets with selections applied do not include any other stores or merchandising types).

What are the expressions I need to use?

Labels (1)
1 Reply
MayilVahanan

Hi @ScottS28 

Try like below

MayilVahanan_1-1605853632331.png

Exp 1: Sum([Sales Revenue])

Exp2: Avg(TOTAL <District> [Sales Revenue])

Exp3: Avg(TOTAL<[Merchandising Type], District> [Sales Revenue])

Exp4: (Column(1)-Column(2))/Column(2)

Exp5: (Column(1)-Column(3))/Column(3)

If you want to ignore the store & merchandise selection, include the set analysis for first 3 exp like below

1.Sum({<Store=,[Merchandising Type]=>}[Sales Revenue])

2.Avg({<Store=,[Merchandising Type]=>}TOTAL <District> [Sales Revenue])

3.Avg({<Store=,[Merchandising Type]=>}TOTAL<[Merchandising Type], District> [Sales Revenue])

hope it helps and give ideas for your requirement

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.