topic Re: How to populate category based on Max(AssignmentDate) in QlikView

How to populate category based on Max(AssignmentDate)

sarfaraz_sheikh — Tue, 27 Feb 2018 08:07:13 GMT

Dear Folks,

Below is my table and i have to show the "employee category name" instead of LEI_Assignemnet_End_date. I have successfully calculated the max assignment date but i want to show the employe category name of max assignment date in chart.

Can anyone please help me how to to bring it on chart level. data and QVW is also attached here for your reference.

Note : we have to do it on front end only because there are too many complex rotations and calculation

implemented in backend.

Example1

In above example it should show confirmed staff only as a employee category because it has max assignment end date. other record should get suppressed.

Exmaple 2:

In this example we should get only highest record based on assignement end date only but it should populate "Resigned " as employee category in chart.

hic‌ gwassenaar‌ please comment

Hope above clarify the requirement. and help would be much appreciated.

Thanks

Sarfaraz

Re: How to populate category based on Max(AssignmentDate)

kaushiknsolanki — Tue, 27 Feb 2018 09:01:00 GMT

Hi,

Try this.

Keep Employee as dimension and below expression for category

If( FirstSortedValue([Employee Category],-LEI_Assignment_End_Date)='-','Resigned',FirstSortedValue([Employee Category],-LEI_Assignment_End_Date))

See attached file for more understanding.

Regards,

Kaushik Solanki

Re: How to populate category based on Max(AssignmentDate)

sarfaraz_sheikh — Tue, 27 Feb 2018 09:28:46 GMT

Thanks Kaushik for your reply.

But if i put Lei_Assignment_date as dimension then all the assignment end date is coming however we want only max assignment date should get populate infronnt of Employee Category.

Incorrect

Corrcet One should be like this .

Thanks

sarfaraz

Re: How to populate category based on Max(AssignmentDate)

kaushiknsolanki — Tue, 27 Feb 2018 09:55:47 GMT

Hi,

It is because you have also added the End date in dimension.

Remove that and you will get what you want.

Regards,

Kaushik Solanki

Re: How to populate category based on Max(AssignmentDate)

kaushiknsolanki — Tue, 27 Feb 2018 09:56:44 GMT

To get the End Date use another expression i.e Max(EndDate)

Regards,

Kaushik Solanki

Re: How to populate category based on Max(AssignmentDate)

sarfaraz_sheikh — Tue, 27 Feb 2018 11:19:41 GMT

Hello Kaushik, Thanks again for your help ....However we have multiple fields included in same pivot table and Max(end date) is giving me values but how we can suppress the record which we do not want in expression column.

Kindly correct me if i am wrong.

Below is the expression i made for max values

Only({<LEI_Assignment_End_Date={"$(=Aggr(Max(LEI_Assignment_End_Date),[Employee No]))"}>}

If(IsNull([Employee Category]) or Len(Trim([Employee Category]))=0 or WildMatch([Employee Category],'*-*'),'Resigned',[Employee Category]))

When i selected then it is showing me the correct value when i deselect then nothing is appearing ...can you please help with expression.

Thanks

Sarfaraz

Re: How to populate category based on Max(AssignmentDate)

kaushiknsolanki — Tue, 27 Feb 2018 11:22:27 GMT

Hi,

Try maxstring instead of only.

Regards,

Kaushik Solanki

Re: How to populate category based on Max(AssignmentDate)

sarfaraz_sheikh — Tue, 27 Feb 2018 11:36:06 GMT

Tried MaxString, concat etc but it same result ....when deselect everything getting null

Thanks

Sarfaraz