how to get first nth date in pivot table?

[jsingh71]

HI ALL,

How I'll get the first nth date in Pivot table.

For example I have below sample date:

Date                    Sales

01-01-2014          100

02-01-2014          101

03-01-2014          100  

04-01-2014          100 

---------------          ------    and so on.

Now I want to display only first 3 date and their respective sales in pivot table.

Please help on this.

Thanks in advance

[#2]

I would say that you need to use a Set Analysis:

sum ({<Date = {"< xxxx"}> } Sales)

or if the date is related to a key

sum({<Date_Key ={" <= 3 "}> } Sales)

Fabrice

[#3]

Hi

or use calculated dimension to fix the set of dates you want

Chris

[Nicole-Smith · Accepted solution]

You can use the following as a calculated dimension (also see attached example.qvw):

=aggr(if(rank(Date)-count(total distinct Date)>-3,Date),Date)

[#5]

Excellent

Forgot that possibility !!

Fabrice

[jsingh71]

Hi Fabrice and Chris

Thanks for reply... I tried calculated dimension also but  how I apply Rank function in date dimension.

[Nicole-Smith]

Right like in my post above...

[jsingh71]

thanks Nicole......its working fine...