Ignoring dimension issue within single table Qlik Sense

[arybak]

Hi, 

I am totally new here and I am seeing for some help.  I would appreciate any help. Thanks in advance.

I am working on the table as below:

Emp in Bld # 3      #attended sessions          first session date                        total sessions for Bld #3 since first session date

Smith                                     35                              Dec 1, 2018                                     ?         

Kovalski                                 20                              Jan 30, 2019                                      ?

Watson                                   40                               Nov 20, 2018                                   ?

Janzen                                    19                               Feb 19, 2019                                       ?

I wanted to set analysis for this example because I need to calculate the date of the first session for each employee. First session date needs to represent date after the very first training date for the individual. See example below:

Date of first session - I created a variable called vDateForFirstSession:

Agg(if (Min(if(Match(bld#, 'Buliding 3')  and meeting_status = 'session', meeting_start_date)) >

min (if(meeting_status = 'training', meeting_start_date)), 

min (if(meeting_status = 'session', meeting_start_date)), EmployeeName)

This calculation need to be done because employee also was able to attend the session prior to the first training and I want to exclude that from my calculations. 

Then in the table I want to calculate total number of all possible sessions which took place in the building no. 3 but I want it to be calculated since each particular individual session date.  This is my formula:

Count({<meeting_start_date = {"> $(TimeStamp($(#vDateForFirstSession),'yyyy-mm-dd HH:MM:SS')) "}, 

bld#= 'Buliding 3', meeting_status = 'session'>} total session_occurances)

Table values appears as; 

Emp in Bld # 3      #attended session                    first session  date           total sessions for Bld #3 since Date of First session

Smith                                     35                                            Dec 1, 2018                         208        

Kovalski                                 20                                           Jan 30, 2019                      208        

Watson                                   40                                            Nov 20, 2018                      208        

Janzen                                    19                                            Feb 19, 2019                       208        

I checked no. 208 and it represents total of all sessions for building #3, meeting status=session but the date condition is ignored. When I select each person, the 208 number changes to the number related to each individual. I don't want that. 

What I want to achieve: 

1. correctly calculate total for all session in Bld #3 considering since first session date. 

2. total for all meeting to do not change once name selection is made. 

I really appreciate your help and advise. 

Regards, 

AdamR.

[y_grynechko]

Hey,

Do you want to have the same 'First Visit Date' for every patient? 

[arybak]

Thanks for reply. 

Unfortunately, every person has different First Visit Date. It would way easier when you have the same first session date for all members/employees. 

I think I am straggling  with calculating a total which accepts one condition associated with a person.....and total disregards conditions......

any ideas?

[y_grynechko · Accepted solution]

So how did you save different values under one variable?  

[y_grynechko · Accepted solution]

Sorry, I was away for a weekend. 

I think that the variable solution is not the one you can go with if you need to store many values. 

I usually create something in visits dimension. I assume that you have something similar with visit ID and visit date. Add additional field called FirstVisitFlag with Y and N. Y when the visit is the first visit and later on you can use this flag in the calc.  

[arybak]

THanks again,

You made a good point. what I have now is variable which returns array of values of firstVisitDate for each employee. My problem is that I want to use variable in the table and compare one single value to it. I think this is a bottom of the problem.

SO, what how can I approach it?

Cheers.

[y_grynechko · Accepted solution]

Where do you store one value? Can't you save it as a variable and use? 

This is how I save one value from a table into variable:

CP:
LOAD
variableValue as [Current Period TXT],
Date#(variableValue, 'YYYY-MM-DD') as [Current Period],
Month(Date#(variableValue, 'YYYY-MM-DD')) as [Current Period Month],
Num(Month(Date#(variableValue, 'YYYY-MM-DD'))) as [Current Period Month Num],
Num(Date#(variableValue, 'YYYY-MM-DD')) as [Current Period Num],
Ceil(Num(Month(Date#(variableValue, 'YYYY-MM-DD'))) / 3) as [Current Period Q Num]
FROM [lib://SS EDS Automated/30_QVD_Extract/Variable_A.qvd]
(qvd);

MaxTable:
load Max([Current Period]) as MaxValue,
Max([CP_2]) as MaxValue2
resident CP;

let v_Today = peek('MaxValue', 0, 'MaxTable' ) ;