Limit Dimension in pivot table for date

[#1]

Hi guys,

I have read lots of discussions about limiting dimension in pivot table, anyhow I need to ask:
How can I limit dimension in pivot table to show expressions just for latest 10 dates?

Thank for your valuable help!

[MK_QSL]

Will this help you?

[#3]

oookey, this is totally it!

working like a charm!

Thanks for your big effort, Manish, have a nice weekend!

[#4]

no, no.. it is not working I was too fast.
Im missing some count()s or.. I don't know. but I can see 10 dates available in listBox and 7 listed. something is wrong here.

[MK_QSL]

Ok.... Your given dates are actually timestamp.... as below

04/04/2014 16:02:47

05/04/2014 08:00:45

06/04/2014 08:00:32

07/04/2014 08:00:29

08/04/2014 03:30:07

08/04/2014 08:00:30

08/04/2014 09:05:39

09/04/2014 08:00:27

09/04/2014 14:55:52

09/04/2014 15:01:56

09/04/2014 15:01:57

10/04/2014 02:17:50

10/04/2014 06:45:01

10/04/2014 07:45:52

10/04/2014 08:00:24

11/04/2014 08:00:27

11/04/2014 08:49:50

11/04/2014 09:03:50

Which are the dates you want to show ?

[#6]

which one...
Ill repeat again: pivot table dimension has to be limited to show only 10 most recent dates descending. current selection gives you some set of dates - this has to be limited to list only 10. It can't be any clearer.

[MK_QSL]

Why I am asking is .....

One date has 10 different timestamp, do you want to show last 10 dates or last 10 timestamps?

[#8]

dates. Im converting this timestamps to dates, this is not a thing. I somehow dont belive/dont understand this approach. what is the number what you'll get from variable expression stands for?
do I get it right if I say:

     Max({1}DateID)-10

will give you max DateID of ?all ?current selection ({$}...) and you will subtract 10.

then dimension according to that expression will list dates which have bigger ID than that calculated.

if I got it right, this is wrong way to go, cause dateIDs are like 2,3,10,15,11... for current selection.

[lcontezini]

In Pivot Table you can't limit your dimensions directly trough the tab "Limit Dimension", you can do it only within the Simple Table (classify your dates in ascending order and then limit the dimension to show only the last 10 values).

If you really need to use a pivot table, what you could use is a variable selecting the last 10 dates (in this case you can select any date and the pivot table will keep working fine), and use this variable as dimension.

If date selection is not a problem you just load your last 10 dates in ETL and save it as a new field, then you can use it in the dimension.

Hope it helps

[Athulya]

Hi,

This is a great solution, and it's helped me solve my problem.

I know this is much later than the original post, but I have an extension question to the solution: how can i hide/remove the max value of date from the final table, so that I have max-5 without the max(date) displayed?

Thanks once again!