topic Re: Set Analysis Expressions in QlikView

Set Analysis Expressions

arvindjha2050 — Sat, 16 Nov 2024 04:54:05 GMT

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

Re: Set Analysis Expressions

sunny_talwar — Mon, 24 Dec 2018 13:28:57 GMT

Try this

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

Re: Set Analysis Expressions

arvindjha2050 — Mon, 24 Dec 2018 13:49:14 GMT

Hello Sunny,

Thanks a lot.

Can you explain the syntax.

Regards,

Arvind

Re: Set Analysis Expressions

sunny_talwar — Mon, 24 Dec 2018 13:58:30 GMT

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.