Customiszation of KPI in Qlik sense

[Newbie_QLIK]

Hi Qlik community,

Hope this message finds you well.

I am new user of Qlik sense and I need help with calculating a monthly average of the number of sales by sales department employees (in the profil variable, they are named "Salesman") for the year 2024 in Qlik Sense. The challenge is to ensure that only the employees in the sales department (where profil variable is equal to "Salesman")  who were active (i.e., who had at least one sale in each month) are included in the average calculation. Here’s a detailed overview of my requirements:

1. Data Context: My dataset includes sales records spanning multiple years (e.g., from 2022 to 2024). The key fields are:

   • Employee name: Unique identifier for each employee.
   • Sale_date: Date of the sale.

2. Active Salesman Definition: We do not have a leave date. Instead, an employee is considered active in a month if they have at least one recorded sale in that month. If an employee has no sales recorded for a given month, they should be excluded from the average for that month.

3. Calculation Objective:

   • Step 1: Identify which salesmans (i.e., only "salesman", so I need to filter it to make sure I do not include other employees in the company) were active in each month of 2024 based on sales records.
   • Step 2: Calculate the total number of sales per month for these active salesman.
   • Step 3: Compute the monthly average number of sales per active salesmans for each month.
   • Step 4: Calculate the overall average of these monthly averages for the year 2024 (can we put max year so that it can be automatically updated whenever we update the database?; for example in 2025, it will be automatically converted to 2025; I think we can use max function with the year but haven't figured out how.).

Could you provide a step-by-step approach or example Qlik Sense expressions to achieve this?

Here is an example of the database:

Thank you so much in advance for your help on this question! 🙂

Best wishes,

Newbie

[gomeri]

Hi @Newbie_QLIK,

to respond your request you need to use set analysis in your chart.

For example, if you create a straight table you can add a new columns with this expressions:

Step 1: if(Profile='Salesman', 1, 0) expression to identify the presence of Salesman, then you can filter number rows with number 1

Step 2: SUM({$<Profle={"Salesman"}>}[nb of product]) 

Step 3: AVG({$<Profle={"Salesman"}}>}[nb of product]) in this case you can insert a variable with month in set expression or select single month from filter panel. In alternative, you can normalize a column with month date and add another column with this expression

Step 4:  AVG({$<Profle={"Salesman"}, Sale_date={'$(=Max(Year(Sale_date)))'}>}[nb of product]) 

hope I was helpful to you.

Thanks

[Newbie_QLIK]

Hi Gomeri,

Thank you for taking your time to answer my question.

Sorry for my confusing long text. In fact, my questionis not using table but the KPI indicator (screenshot below). 

I am not sure if your answer can be applied to the KPI indicator as well. If so, could you tell me what it the entire codes to conduct it? I don't think I can directly apply your codes:«

 if(Profile='Salesman', 1, 0) //expression to identify the presence of Salesman, then you can filter number rows with number 1

SUM({$<Profle={"Salesman"}>}[nb of product]) 

AVG({$<Profle={"Salesman"}}>}[nb of product]) //in this case you can insert a variable with month in set expression or select single month from filter panel. In alternative, you can normalize a column with month date and add another column with this expression

AVG({$<Profle={"Salesman"}, Sale_date={'$(=Max(Year(Sale_date)))'}>}[nb of product]) »

I wonder if it is possible to show the average sales per week per salesman (not by salesman name) with KPI indicator? For example, the KPI indicator would be something like 10 sales per week per salesman.

the Calculation Objective was just my procedure to get  the average sales per week per salesman in excel. So please feel free to provide any better procedure to get there.

Thank you again for your time.

Best,

Newbie_Qlik

[Rohan · Accepted solution]

Hi @Newbie_QLIK ,

Try something like : 

{<Profile={"Salesman"},SalesDate={">=$(=date(monthstart(max(SalesDate))))<=$(=date(max(SalesDate)))"}>}

sum(Sales)/count(distinct EmployeeName)

Basically, here we have defined a set where Profile is restricted to Salesman & SalesDate is restricted to max Month.

Regards,

Rohan.

[Newbie_QLIK]

Thank you Rohan!

It worked perfectly! Thank you for taking your time to share your knowledge!

I know understant what is set analysis({})

Best,

Newbie_Qlik