Qlik Community

Deployment Framework

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
vengadeshpalani
Creator
Creator

Expression To get Presented EmpCount

Hi Community,

I'm new to Qlik View, I faced this case, and I need your help please.

EmpIDEmpNameJoining DateRelevingDate
001Bose05/05/201405/05/2015
001Bose01/01/201607/01/2016
002Siva02/02/201509/10/2016
003Chandra02/01/2015
004Cp10/10/2016
005JP05/05/201505/05/2016

I have month and year master calendar Filter Please tell the expression when I select any one of month and year, I want the present EmpID Count only for the selected Month and year

1 Solution

Accepted Solutions
sunny_talwar

Like this?

Capture.PNG

Script:

Table:

LOAD EmpID,

  EmpName,

  [Joining Date] as Joining_Date,

  RelevingDate,

If(Len(Trim(RelevingDate)) = 0, Today(), RelevingDate) as Releving_Date

FROM [https://community.qlik.com/thread/241033]

(html, codepage is 1252, embedded labels, table is @1);

MinMax:

LOAD Min(Joining_Date) as Min,

  Max(Releving_Date) as Max

Resident Table;

LET vMin = Peek('Min');

LET vMax = Peek('Max');

DROP Table MinMax;

Calendar:

LOAD Date,

  MonthName(Date) as MonthYear,

  Year(Date) as Year,

  Month(Date) as Month;

LOAD Date($(vMin) + IterNo() - 1) as Date

AutoGenerate 1

While $(vMin) + IterNo() - 1 <= $(vMax);

Left Join (Table)

IntervalMatch (Date)

LOAD Joining_Date,

  Releving_Date

Resident Table;

Assuming you already have a master calendar, the script is red is what you will need to make a connection between your master calendar and the fact table.

View solution in original post

2 Replies
sunny_talwar

Like this?

Capture.PNG

Script:

Table:

LOAD EmpID,

  EmpName,

  [Joining Date] as Joining_Date,

  RelevingDate,

If(Len(Trim(RelevingDate)) = 0, Today(), RelevingDate) as Releving_Date

FROM [https://community.qlik.com/thread/241033]

(html, codepage is 1252, embedded labels, table is @1);

MinMax:

LOAD Min(Joining_Date) as Min,

  Max(Releving_Date) as Max

Resident Table;

LET vMin = Peek('Min');

LET vMax = Peek('Max');

DROP Table MinMax;

Calendar:

LOAD Date,

  MonthName(Date) as MonthYear,

  Year(Date) as Year,

  Month(Date) as Month;

LOAD Date($(vMin) + IterNo() - 1) as Date

AutoGenerate 1

While $(vMin) + IterNo() - 1 <= $(vMax);

Left Join (Table)

IntervalMatch (Date)

LOAD Joining_Date,

  Releving_Date

Resident Table;

Assuming you already have a master calendar, the script is red is what you will need to make a connection between your master calendar and the fact table.

vengadeshpalani
Creator
Creator
Author

Thanks @sunny