Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Simon4
Creator
Creator

set analysis not working when multiple values selected

hi all,

my set analysis is working properly when one value is selected, but it does not work on the whole selection.

Simon4_0-1675179144727.png

Simon4_1-1675179156236.png

to determine timetable_id i am using:

ONLY({1<STARTDATE = {'$(=$(vStartdate))'}>}timetable_id)

where vStartdate = max(if(STARTDATE<(vPeildatum)+1,STARTDATE))

this is my data load:

LOAD
"PK_R_EMPLOYEE" as Employee_id,
CODE as Employee_code;

SQL SELECT
"PK_R_EMPLOYEE",
CODE
FROM ".dbo."V_R_EMPLOYEE";

tbl_timetable:
LOAD "FK_EMPLOYEE" as Employee_id,
"FK_TIMETABLE" as timetable_id,
date(STARTDATE) as STARTDATE;
SQL SELECT "FK_EMPLOYEE",
"FK_TIMETABLE",
STARTDATE
FROM "V_R_TIMETABLEOFFSET";

 

left join tbl_timetable:
LOAD "FK_TIMETABLE" as timetable_id,
"FK_WORKDAY" AS WORKDAY_ID,
SEQUENCENR,
"PK_R_TIMETABLEWORKDAY";
SQL SELECT "FK_TIMETABLE",
"FK_WORKDAY",
SEQUENCENR,
"PK_R_TIMETABLEWORKDAY"
FROM "V_R_TIMETABLEWORKDAY";

left join tbl_timetable:
LOAD CODE,
DESCRIPTION,
(DEFAULTHOURS/10000000)/(60*60) AS DEFAULTHOURS,
"PK_R_WORKDAY" AS WORKDAY_ID;
SQL SELECT CODE,
DESCRIPTION,
DEFAULTHOURS,
"PK_R_WORKDAY"
FROM "V_R_WORKDAY";

how can i fix this?

 

kind regards,

 

Simon de Vrieze

 

 

 

Labels (1)
5 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi Simon,

My guess would be that the reason for this problem is the Max() function that returns a numeric value rather than a formatted Date value, and that's no good for Set Analysis. For starters, I'd try to enclose the Max() function within the Date() function to format the result as a proper Date.

Then, I'd like to point out that using a formula with a Max(IF()) inside of your Set Analysis kind of defeats the purpose of using Set Analysis at all. There should be easier ways to achieve the same logic. If you can formulate the logic in plain English, I could help you formulate the expression

Also, allow me to invite you to my lecture on Set Analysis and AGGR() at the Masters Summit for Qlik,  that will take place on-line on March 1st. Check out agenda for all 5 advanced sessions on Qlik development methodologies. 

Cheers, 

Simon4
Creator
Creator
Author

Dear Oleg ,

 

Thank you for responding. 

 

I want to achieve the following: 

I have a variable reference date that automatically is set to today() called vPeildatum.

I want the timetable_id for the max(startdate) < vPeildatum. 

Just like on the first picture, only then for all of the employee_code.

 

I tried your suggestion of enclosing the max() in date but it does not work. 

Is this enough information? 

I will seriously considering registering for the summit, because I can certainly use it....

 

Thank you in advance!

kind regards,

 

Simon de Vrieze

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi Simon,

So, let me rephrase your requirement: for each Employee, you want to know the Timetable ID, for which the Max(StartDate) is less than a certain date (i.e. today) - correct? What happens if you have multiple Timetable IDs that satisfy this condition? With multiple possible values, the function Only() returns NULL.

Also, the Set Analysis condition cannot be verified separately for each Employee ID (which is the dimension in your table) - it's validated globally, outside of the chart boundaries.

For starters, try to replace  the function only() with another function that would work better with multiple values. You could use Min, Max, MinString, MaxString, Concat, FirstSortedValue. Then, see if that gets you closer to your goal.

Simon4
Creator
Creator
Author

I am unable to get to the desired outcome, any other suggestions?

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

The only suggestion is to describe your problem more specifically. "I am unable to get to the desired income" doesn't help anyone understand what is the problem.