Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ihuynhi
Contributor II
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

ex1.PNG

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

ex3.PNG

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:

ex4.PNG

Desired result:

ex6.PNG

 

Labels (4)
2 Replies
edwin
Master II
Master II

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:

edwin_0-1654959605694.png

 


//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;
load 1 AutoGenerate(1);

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:
load
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)
load
CalendarKey, QuarterKey, 'PRIOR' as QuarterType
Resident T2Quarters
where priorRNum=rnum-1;

drop tables T1Quarters, T2Quarters;

 

 

the following is just the fact data:

edwin_1-1654959668708.png

 

this shows the relationship between prior and current:

edwin_2-1654959704612.png

this is the expression you are looking for:

edwin_3-1654959758378.png

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.



 

vinieme12
Champion III
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.