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.
In the bottom table that has Cal_Year_Quarter it shows 4 rows with different target units that correspond with different LinkEmplProdObj_Year_Quarter :
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.
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:
I've tried changing my expression to :
sum( aggr( If( Cal_Date >= max(LinkEmplProdObj_Start_Date) and Cal_Date <= max(LinkEmplProdObj_End_Date),
,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