Skip to main content

New to Qlik Sense

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

Announcements
QlikWorld 2023, a live, in-person thrill ride. April 17 - 20, 2023, in Las Vegas! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
ElsKnockaert
Contributor III
Contributor III

Can't link objective to master calendar

Good morning

I've been struggling with this for a while now. The customer has objectives he wants to see in Qlik Sense:

- Target Units sold per product Group (Prod_level_3),  per quarter,  per employee (Empl_Id)

I've tried to integrate this table from the database into the star-model. It worked to link it to the Employee-table and Product-table but am unsuccessful into linking it to the master calendar.

In my Objectives table I have a field  

- LinkEmplProdObj_Date : Which shows the start date of the quarter in format YYYY-MM-DD

- LinkEmplProdObj_Year_Quarter: Which shows the  quarter in format YYYY 'Q'Q                        (example: 2022 Q2)

I want to link Cal_Year_Quarter (same format as 2022 Q2) from the master calendar with LinkEmplProdObj_Year_Quarter from the objectives table.

Reason why I want to do this is to resolve following example:

Don't mind the left bar chart.

Right you see 2 tables. Only the green rows are correct and should be allowed to be shown while I filter on Cal_Year_Quarter = 2022 Q2.

The top table shows LinkEmplProdObj_Year_Quarter in column Objective Year Quarter and the bottom table shows Cal_Year_Quarter in column Cal Year Quarter.

ElsKnockaert_0-1650961182218.png

 

In the bottom table that has Cal_Year_Quarter it shows 4 rows with different target units that correspond with different LinkEmplProdObj_Year_Quarter  :

ElsKnockaert_4-1650960393356.png

In the top table that shows LinkEmplProdObj_Year_Quarter   it again shows 4 rows but should only show the top one because of my filter on Cal_Year_Quarter = 2022 Q2.

 

In addition to that mess, if I select multiple quarters, the formula in Qty Ordered (not vrac) doesn't work any longer. It should show 14 for 2022 Q2 and 52 for 2022 Q1. Instead it adds both selections into 2022 Q2.  And in the bottom table it just shows the same 4 rows, twice now. Once all 4 for 2022 Q2 and once all 4 for 2022 Q1.

ElsKnockaert_5-1650960771029.png

 

The expression in Qty Ordered (not vrac) is:  

sum(aggr(sum($(=vProductMonitoringGroup) Order_Qty),Empl_Id,Prod_Level_3,Cal_Year_Quarter))

with vProductMonitoringGroup =  

{< Cal_Year_Quarter = P(LinkEmplProdObj_Year_Quarter ), Prod_Level_3_Id = P(LinkEmplProdObj_Group_Id) >}

 

I would like to add the qvf but due to GDPR this raises issues.

This is how i tried to add the table into the star model. It works for employees and product but not for cal_year_Quarter:

ElsKnockaert_6-1650960969451.png

ElsKnockaert_7-1650961052046.png

 

I've tried changing my expression to :

sum(
aggr(
If( Cal_Date >= max(LinkEmplProdObj_Start_Date) and Cal_Date <= max(LinkEmplProdObj_End_Date),

sum(Order_Qty)
)

,Empl_Id,Prod_Level_3_Id, Cal_Date)
)

The result of which gives me the right amount of ordered amount for the row of Cal_Year_Quarter = 2022 Q2. But 0 for the other rows. My filter for this is on the field Cal_Year_Quarter: 2022 Q2 and 2022 Q1

ElsKnockaert_0-1651041893967.png

 

Labels (4)
0 Replies