Set Analysis Expressions

[arvindjha2050]

Hello Team,

Below is my inline load script :

Load * Inline
[
SYear,Email,Rating
2017,A,81
2017,B,82
2017,C,76
2017,D,58
2017,E,69
2016,A,91
2016,E,56
2016,X,23
2016,Y,96
2015,C,87
2015,M,74
2015,N,91
];

My query is that i want a line chart which shows Avg(Ranking) for Repeat Emails.

So final output would be :

2017 : Avg(Ranking) for A,E,C

2016 : Avg(Ranking) for A,E

2015 : Avg(Ranking) for C as only that is common between 2015 and 2017.

So here we will take max year i.e. 2017 in above case as base and calculate averages for common between them

Thanks,

Arvind

[sunny_talwar · Accepted solution]

Try this

=Avg({<Email = P({<SYear = {"$(=Max(SYear))"}>})*P({<SYear = {"$(='<' & Max(SYear))"}>})>}Rating)

[arvindjha2050]

Hello Sunny,

Thanks a lot.

Can you explain the syntax.

Regards,

Arvind

[sunny_talwar]

All the syntax is saying is that to show Email which are available in max year as well as in one of the years before max year.