New to Qlik Sense

[ihuynhi]

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:

[edwin]

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;
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:

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.

[vinieme12]

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))    )  )