How to create quarterly averages for 6 different categories

[Kingofghost33]

Hi,

I'm actually learning QlikSense. I'm trying to make quarterly averages over 6 different categories. 

My Data : I have 6 breakdown categories.  Breakdowns have an ID and a date.

famille_sa : 6 categories of breakdown

id_crft : id of the breakdown

Date_du_fait_technique : Date of the breakdown

My formula for quarters : =year(Date_du_fait_technique) & 'Q-' & ceil(month(Date_du_fait_technique)/3)

What i'm trying to do : I would like to create an indicator that compares the last quarter of the last year with the quarters of previous years.

Like this : Q1-2023 VS (Q1-2022+Q1-2021+Q1-2020/3)

NB : I'm attaching screenshots of what I've done.

Thanks for your support.

Sam

[Rohan · Accepted solution]

Hi @Kingofghost33 ,

If I understand your scenario correctly, You should create the Quarter & Year Column in the backend & create a variable vQToggle='Q-1'. Use this variable in the variable input & create 4 drop down options, Q-1,Q-2,Q-3,Q-4 & try 4 measure columns like :

Current Avg : 

Avg(aggr(count({<Year={"$(=year(yearstart(max(Date_du_fait_technique))))"},Quarter={"$(vQToggle)"}>}id_crft),famille_sa))

Previous Avg : ((Avg(aggr(count({<Year={"$(=year(yearstart(max(Date_du_fait_technique),-1)))"},Quarter={"$(vQToggle)"}>}id_crft),famille_sa))+

Avg(aggr(count({<Year={"$(=year(yearstart(max(Date_du_fait_technique),-2)))"},Quarter={"$(vQToggle)"}>}id_crft),famille_sa))+

Avg(aggr(count({<Year={"$(=year(yearstart(max(Date_du_fait_technique),-3)))"},Quarter={"$(vQToggle)"}>}id_crft),famille_sa)))/3

Let me know if it works out for you.

Regards,

Rohan.

[Kingofghost33]

Thanks for your great answer !

As a beginner beginner, I'm trying but I'm having trouble creating the 4 drop down options and also the variable.

I have to go to the bottom left to create the vQToggle='Q-1' variable? how do I use the input variable?

What do you call the "backend" , it is a blank sheet?

NB : Screenshots in attachment are good?

Thanks

Sam

[Rohan]

Hi,

By Backend, I meant the Load script. there in your calender table add the columns.

Variable Definition : 

Use this following object from extension bundle :

Regards,

Rohan.

[Kingofghost33]

Hi @Rohan ;

Thanks for your answer.

I tried all the week-end but it's impossible for me to do it. Sorry.

What do you think about the attachments? what have I done wrong?

Thanks a lot

Sam 🙂

[Rohan]

Hi Sam,

Two things :

Firstly Create only :  'Q-' & ceil(month(Date_du_fait_technique)/3) as Quarter & put year in a different field.

Secondly : You have to create in the Front End. using the assets pannel. & the values that you have set for the button have to be : 'Q-1', 'Q-2', 'Q-3' & 'Q-4' respectively.

Try these things & check.

Regards,

Rohan.

[Kingofghost33]

Hi @Rohan !

Thanks for your answer.

It's OK! I create the quarter and year variables independently as you can see below.

But i didn't understand the second part of your answer.  What formula i need to put in Q-1 Q-2 Q-3 & Q-4 drop-down options?

 

Thanks

Sam

[Rohan]

Hi Sam,

Please refer below image :

Regards,

Rohan.

[Kingofghost33]

Ahah i'm terrible. Thanks @Rohan , it's work perfectly. Have a nice day.

[Rohan]

@Kingofghost33 Glad it worked. Have a nice day 🙂