What function to use? Simple Problem!

[#1]

Hi,

I have 3 fields

Name,Granted_Date, ManagerName.

I want to display the value of field 'ManagerName', based on the the values of Name and Granted Date. suppose I select a 'Name', it has only one granted date but multiple managers name. I want to display only that manager name who was the manager at the granted date and not the whole list of multiple managers associated with that name.

Thanks . Any help would be appreicated.

[Miguel_Angel_Baeyens]

Hi,

If all three fields are in the same table in QlikView, then once you select Name and Granted_Date, if there is only one possible value for ManagerName, then this will be displayed. But I'm assuming that you already tried something liek that and that it didn't work, so please elaborate on what do you want to return, what objects are you using and post some simple data that help us to check what you need to change.

Check the function FirstSortedValue() that can be used in either the script or a chart. Check this post among many others on how to use it with a similar issue.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#3]

Hi Miguel,

The problem is I don't want my user to select the granted date column. It should display that this is the manager on the granted date.

Example:

Name, Granted Date, ManagerNames, Date

ABC, 9/20/2010, RM1, 09.20.2011

ABC, - , RM2, 09.21.2011

ABC,-, RM3, 09.22.2011

This date is connected to the master calender. Now, if I select name ABC, it should see the grant date and display that manager at that time is RM1. It could be displayed in a text box as well or anything.

[#4]

Sorry , the granted date is also repeated with each record. So its like

Name, Granted Date, ManagerNames, Date

ABC, 9/20/2010, RM1, 09.20.2011

ABC, 9/20/2010 , RM2, 09.21.2011

ABC,9/20/2010, RM3, 09.22.2011

[Miguel_Angel_Baeyens]

Hi,

What date is associated to the master calendar? If users cannot select Granted Date, are they able to select "Date"? If not, what criteria should be used to know what ManagerName is correct once they select a Name? I. e.: The following will work in a text object or a chart expression, but I guess you want some other criteria to apply

=MaxString({< [Granted Date] = {'$(=Max([Granted Date]))'}>} ManagerNames) 

Using your example above, it will return "RM1" as expected.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[Miguel_Angel_Baeyens]

Another approach to my first answer below is using FirstSortedValue() as I mentioned above. Did you check the post?

=FirstSortedValue(ManagerNames, Date)

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#7]

Hi Miguel,

The Date field is connected to the Calender and not the Granted Date. As you could see the right data set below:

Name, Granted Date, ManagerNames, Date

ABC, 9/20/2010, RM1, 09.20.2010

ABC, 9/20/2010 , RM2, 09.21.2010

ABC,9/20/2010, RM3, 09.22.2010

The granted date is same for the name ABC as 9/20/2010 in each record and at that granted date the manager was RM1. I'm sorry for posting the wrong data in my above post. So , this 'Date' is actually storing data for each day. And at the 'Date' Value 09.20.2010 (which is also the granted value) the manager was RM1. That is all I want to display.

I think I just want the right set analysis expression for this.

So, when I choose my name , it displays all the managers RM1, RM2, RM3. All I want is to display that RM1 was the one on granted date.

[qliksus]

give some thing like

only({<Granted date = {$(=concat(Date,','))}>} ManagerNames )

[#9]

Hi,

I have tried this statement but couldn't get any output. Can you tell me why have you used concat statement a

Thanks.