topic set analysis in concat/aggr function? in QlikView

set analysis in concat/aggr function?

Anonymous — Thu, 11 Aug 2011 09:13:52 GMT

Hi!

I'm trying to display the Team name that a person belongs to. Problem is that the person can belong to several teams in the past. What i want to see is only the team that the person blongs to today.

This is the one i'm currently using

concat(distinct aggr(if(sum(Points)>0,Team),Name,Team),',')

(Points must be moore than 0 by the way, hence the expression. )

But that gives me all the teams the person belongs to. how do i do this to include the Date issue?

//A.

set analysis in concat/aggr function?

kaushiknsolanki — Thu, 11 Aug 2011 09:21:33 GMT

Hi,

There can be some other way to do this.

So can you please tell us something about your data structure.

Meaninng how you find who is in which team today. (Manually)

Regards,

Kaushik Solanki

set analysis in concat/aggr function?

Anonymous — Thu, 11 Aug 2011 09:49:20 GMT

Team is in the fact table ande names in a dimension table.

So when i select a name the team that person belongs to is possible choices. And like i said, over time there can be more than one team but if you pick a date there will always only be one team for that selected person.

Does it help you?

set analysis in concat/aggr function?

kaushiknsolanki — Thu, 11 Aug 2011 09:57:58 GMT

Hi,

Then how you are willing to show that. I mean can you please tell me in detail with example, what you want to achieve.

Regards,

Kaushik Solanki

set analysis in concat/aggr function?

erichshiino — Thu, 11 Aug 2011 10:12:16 GMT

Hi,

You can use indirect set analysis, like this:

=Concat( {<TEAM = {"=SUM(POINTS)>0"}>} DISTINCT TEAM,',')

set analysis in concat/aggr function?

Anonymous — Thu, 11 Aug 2011 10:30:09 GMT

sorry again.

I have a chart with name as dimension and then the expression to basically show possible team. but i only want to show one possible team, hence, the team that they have points in in the nearest future.

So i want it in text. The Team. and that's why i'm using the concat function.

//A.

set analysis in concat/aggr function?

erichshiino — Thu, 11 Aug 2011 10:34:50 GMT

Doesn't this concat work?

=Concat( {<TEAM = {"=SUM(POINTS)>0"}>} DISTINCT TEAM,',')

Regards,

Erich

set analysis in concat/aggr function?

kaushiknsolanki — Thu, 11 Aug 2011 10:34:56 GMT

Hi,

Try this.

In your chart put expression as

maxstring({<Date = {"$(=Max(Date))"}>}Team)

Regards,

Kaushik Solanki

set analysis in concat/aggr function?

Anonymous — Thu, 11 Aug 2011 11:16:57 GMT

Erich: your solution still shows more than one team unfortunately.

Kaushik: Yours is close but those who haven't had points (those are connected to dates) since last month or more ends up with no team at all

set analysis in concat/aggr function?

kaushiknsolanki — Thu, 11 Aug 2011 11:38:44 GMT

Hi,

I didnt get what you said. Can you please explain with example.

Regards,

Kaushik Solanki

set analysis in concat/aggr function?

perumal_41 — Thu, 11 Aug 2011 12:04:35 GMT

Upload ur Qvw file

Re: set analysis in concat/aggr function?

erichshiino — Thu, 11 Aug 2011 12:24:16 GMT

I'm not sure about the criteria to show only one,

but you can use set analysis with maxstring as well...

=maxstring( {<TEAM = {"=SUM(POINTS)>0"}>} TEAM)

EDIT : Correction in the expression

set analysis in concat/aggr function?

Anonymous — Tue, 16 Aug 2011 08:40:09 GMT

This one is close but the problem here is that maxstring collects the last team and the teams are in an alfabetical order so it shows the wrong value when it comes to terms of last team-membership date-wise.