# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Cloud Maintenance is scheduled between March 27-30. Visit Qlik Cloud Status page for more details.
cancel
Showing results for
Did you mean:
Contributor II

## How to calculate percentage difference per quarter per group with chart functions

I am new to Qlik so apologies if I use the wrong terms. I have a dataset with a following form

and I have graphed it in a following way

Dimensions:  Year & ' ' & Q and Area

Measure: Avg(sales)

So that I get average of sales per area for each quarter. It looks like this

Now I want to get a percentage of difference from quarter to quarter for these with a chart function.

I have tried for the measure to create expressions

Avg(TOTAL <YearQ, Area> sales)/Above(Avg(TOTAL <YearQ, Area> sales)) - 1

and

Avg(sales)/Above(Avg(sales))-1

but they give me wrong numbers. Here's demonstrated on Excel what I would like as a result:

Average of sales per area for each quarter:

Desired result:

Labels (4)

• ### Visualization

2 Replies
Master

uld solve this by moving the relationship between current and previous quarters to the data model.  this way the chart expressions are simpler, easire to understand/maintain, and faster.

build a bridge between your calendar anmd the fact table:

``````
//this is your calendar
NoConcatenate
Calendar:
load *, Year & '-' & Quarter as CalendarKey inline [
Year, Quarter
2020, 1
2020, 2
2020, 3
2020, 4
2021, 1
2021, 2
2021, 3
2021, 4
];

//the following just builds a Fact table based on random generation of data
NoConcatenate
data:
load * inline [
Area, Store
A, a1
A, a2
B, b1
B, b2
B, b3
C, c1
C, c2
];

inner join (data)
load CalendarKey as QuarterKey resident Calendar;

inner join (data)
load floor(rand()*10) as num
while iterno()<20;

NoConcatenate
Data:
load *, floor(rand()*100) as Sales
Resident data
where num<7;

drop table data;

//this starts the build of the bridge between your calendar and your fact
// Calendar key anchors your fact to the calendar
//for the same quarter and year in calendar, you will link 2 records in the fact
//one is linked to the current fact record
//2nd is linked to the prior record/quarter
//so selecting a quarter in the caledar you are linked to the current and prior data
//then in your set analysis qualify your expression by quarter type
NoConcatenate
T1Quarters:
load distinct CalendarKey, Year, Quarter
Resident Calendar;

NoConcatenate
T2Quarters:
load CalendarKey, Year, Quarter, RowNo() as rnum
Resident T1Quarters
order by Year, Quarter;

inner join (T2Quarters)
load CalendarKey as QuarterKey, Year as priorYear, Quarter as priorQuarter,rnum as priorRNum
Resident T2Quarters;

//if calendar quarter = fact qurater then thats the current quarter in the fact
NoConcatenate
Quarters:
CalendarKey, QuarterKey, 'CURRENT' as QuarterType
Resident T2Quarters
where CalendarKey=QuarterKey;

//if fact quarter = current quater - 1 then thes the prior quarter in the fact
Concatenate (Quarters)
CalendarKey, QuarterKey, 'PRIOR' as QuarterType
Resident T2Quarters
where priorRNum=rnum-1;

drop tables T1Quarters, T2Quarters;

``````

the following is just the fact data:

this shows the relationship between prior and current:

this is the expression you are looking for:

if you add your other dimensions then the grouping will be by Area and Store.

the idea is create a relationship between your fact records based on your calendar.  this concept is similar to the AS OF TABLE:
https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130

hope that helps.

Champion III

this could just be a bracketing issue, try below

=1-(avg(sales)/ above(avg(sales)))

Or

=1-(   (sum(sales)/count(distinct store))  /   above(  (sum(sales)/count(distinct store))    )  )

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Tags
Community Browser