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!

[its_anandrjs]

Dimension limit feature in QV 11.0 version or for straight tables not for pivot my correction for this but you can use if conditions for this and load minimum data.

[#3]

not for a PIVOT TABLE.
Calculated expression needed!

[its_anandrjs]

Suppose you want to limit your dimension to the latest 10 days

Eg:-

In Expression write

=sum({<DATE={">=$(ToDate) <=$(FromDate)"}>} Sales )

[MK_QSL]

Dimension Limit is not available in QlikView Pivot Table...

You need to add calculated dimension something like below..

=IF(Date>=Date(Today()-10),Date)

[#6]

can this be modified to show latest 10 dates OF ALL DATES THAT 'EXEC_DATE' FIELD CONTAINS?

[MK_QSL]

Don't know how to do by Calculated Dimension but yes, you can achieve the same using Set Analysis in Expression..

Consider that you want SUM(Sales) for last 10 EXEC_DATE

Create a Pivot Table

Dimension

EXEC_DATE

Expression

SUM({<EXEC_DATE= {">$(=Date(Max({1}EXEC_DATE)-10))"}>}Sales)     // This will give you last 10 days irrespective of your EXEC_DATE selection.

and

SUM({<EXEC_DATE= {">$(=Date(Max(EXEC_DATE)-10))"}>}Sales) // This will based on selection

Hope this helps....

[its_anandrjs]

You can limit your dimension by any variable also

Let vLatestDate =  Date(Today()-10,'DD/MM/YYYY');

and use in dimension like

=IF(YourDateField = $(vLatestDate), Date)

[#9]

well, this would give me a 10 last dates from now.

what I want is 10 latest dates of all of EXEC_DATE field.

imagine that this field can contain dates from the previous day, three dates from a previous mont, few dates from a previous year etc... and I need a 10 latest of this values - values in EXEC_DATE.

[its_anandrjs]

In database how you identify the dates is latest in the EXEC_DATE if there is a way to identify the latest date so if in load script a Flag field is created and according to Flag field read the dates. Like EXEC_DATE , LatestFlag among with Flag field and in dimension fetch only those flags like 1 for latest and 0 for old

If(LatestFlag = 1, EXEC_DATE) in dimension you use that

Regards