Announcements
cancel
Showing results for
Did you mean:
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:

 District Store Merchandising Type Sales Revenue A 1 Modern 5000 A 2 Modern 3000 A 3 Legacy 1000 A 4 Modern 1000 B 1 Legacy 2000 B 2 Legacy 3000 B 3 Modern 5000 C 1 Modern 500 C 2 Legacy 1500 C 3 Modern 1000 C 4 Legacy 1000

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:

 District Store Merchandising Type Sales Revenue District Average District Merchandising Average % Above District Average % Above District Merchandising Average A 1 Modern 5000 2500 3000 100% 67% A 2 Modern 3000 2500 3000 20% 0% A 3 Legacy 1000 2500 1000 -60% 0% A 4 Modern 1000 2500 3000 -60% -67% B 1 Legacy 2000 3333.33 2500 -40% -20% B 2 Legacy 3000 3333.33 2500 -9% 20% B 3 Modern 5000 3333.33 5000 50% 0% C 1 Modern 500 1000 750 -50% -33% C 2 Legacy 1500 1000 1250 50% 20% C 3 Modern 1000 1000 750 0% 33% C 4 Legacy 1000 1000 1250 0% -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)
• ### Qlik Sense

MVP

Try like below

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