topic Need Help on Rank Function in QlikView

Need Help on Rank Function

Thu, 22 Apr 2010 12:30:41 GMT

Hi All,

Need Help on below issue.

I need show value in pivot table with higest rank 3. ranking should be with based on my max of month. Till here i dnt have problem.

Problem is i need to show the total for all irrespective of the ranks.

Pl. look into my application.

i need a total for

01-01-2009 = 200

and

01-02-2009 = 135

but now i`m getting

01-01-2009 = 180

and

01-02-2009 = 130

Since i have restricted my value to top 3 i.e Rank 3.

Hope i have explained my problem clearly. please reply for further query

- Sridhar

Need Help on Rank Function

Thu, 22 Apr 2010 13:50:55 GMT

Hi All,

not able to upload the application. Pl find the details below

Data:

LOAD * INLINE [
POSNR, Duration, Temp,Month
1, 5, 30,1-1-2009
2, 7, 100,1-1-2009
3, 3, 50,1-1-2009
4, 2, 20,1-1-2009
5, 5, 60,1-2-2009
6, 7, 50,1-2-2009
7, 3, 20,1-2-2009
8, 2, 5,1-2-2009

];

O/P which i`m getting

RANK DURATION 01-01-2009 01-02-2009

1 5 50 60

2 7 100 50

3 3 50 20

Total 180 130

Required O/P

RANK DURATION 01-01-2009 01-02-2009

1 5 50 60

2 7 100 50

3 3 50 20

Total 200 135

Expression whihc i`m using to find my rank is

=if(aggr(rank( Sum (if(Monthname(Month) = Monthname(VMax),Temp))),Duration)<=3,

aggr(rank( Sum (if(Monthname(Month) = Monthname(VMax),Temp))),Duration))

Pl. reply for further clarification.

- Sridhar

Need Help on Rank Function

Thu, 22 Apr 2010 15:52:39 GMT

Finally did it with below formula.

if(Dimensionality() = 2,Sum (Temp),aggr(Sum ( Temp),Month))

- Sridhar