topic Re: Above Function with in Set Analysis in QlikView

Above Function with in Set Analysis

timanshu — Sun, 22 Feb 2015 08:19:37 GMT

Hi all,

Can I write Above() function within set analysis?? If Yes then How?

Actually I want to limit dimensions in straight table based on the condition that, if a column value in a row is same as that in above row, I want to exclude the row.How to obtain this with expression?

Example :

Input:

date	id	value

01/05/2015	102	19
01/06/2015	103	25
01/07/2015	105	48
01/08/2015	105	65
01/09/2015	106	4
01/10/2015	107	38

Output should be :

date	id	value

01/05/2015	102	19
01/06/2015	103	25
01/07/2015	105	48
01/09/2015	106	4
01/10/2015	107	38

Kindly Note:- Here value is the only expression.

Re: Above Function with in Set Analysis

Sun, 22 Feb 2015 09:36:29 GMT

Hi Timanshu,

I have managed to get the output as above but i didnt use the above function.

I just assigned '1'as flag to each record and then aggregated it by id.

then i created an expression which checks the value of aggregation....for the duplicate id field you will get aggr as null.

Also i created a calculated expression to show the date only for unique ids.

Attaching the qvw.

Hope it helps.:)

Re: Above Function with in Set Analysis

maxgro — Sun, 22 Feb 2015 09:45:03 GMT

if(date=min(TOTAL <id> date), sum(value))

Re: Above Function with in Set Analysis

MK_QSL — Sun, 22 Feb 2015 10:37:14 GMT

Create a Straight Table

Dimension

date

expression

=Aggr(Min(value),id)

Re: Above Function with in Set Analysis

MK_QSL — Sun, 22 Feb 2015 10:51:18 GMT

One more way to do it...

Create a Straight Table

Dimensions

1) Calculated Dimension

Aggr(MIN(date),id)

2) id

Expression

Min(value)

Re: Above Function with in Set Analysis

timanshu — Sun, 22 Feb 2015 11:11:44 GMT

Thanks Shashi

Your Solution gives the same output, however I only need to do this work by changing expressions.

Re: Above Function with in Set Analysis

timanshu — Sun, 22 Feb 2015 11:23:42 GMT

Thanks Massimo

It works.

Re: Above Function with in Set Analysis

timanshu — Sun, 22 Feb 2015 11:27:02 GMT

Hi Manish,

Thanks for reply.

This actually gives me minimum value but It should show value that occurs First according to date.

Re: Above Function with in Set Analysis

MK_QSL — Sun, 22 Feb 2015 11:37:55 GMT

Then my 2nd solution should work.