topic Re: Limit Dimension in pivot table for date in QlikView

Limit Dimension in pivot table for date

Thu, 10 Apr 2014 14:46:33 GMT

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!

Re: Limit Dimension in pivot table for date

its_anandrjs — Thu, 10 Apr 2014 14:49:35 GMT

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.

Re: Limit Dimension in pivot table for date

Thu, 10 Apr 2014 14:59:12 GMT

not for a PIVOT TABLE.
Calculated expression needed!

Re: Limit Dimension in pivot table for date

its_anandrjs — Thu, 10 Apr 2014 15:05:53 GMT

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

Eg:-

In Expression write

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

Re: Limit Dimension in pivot table for date

MK_QSL — Thu, 10 Apr 2014 15:07:41 GMT

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

You need to add calculated dimension something like below..

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

Re: Limit Dimension in pivot table for date

Thu, 10 Apr 2014 15:14:43 GMT

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

Re: Limit Dimension in pivot table for date

MK_QSL — Thu, 10 Apr 2014 15:26:19 GMT

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....

Re: Limit Dimension in pivot table for date

its_anandrjs — Thu, 10 Apr 2014 15:26:43 GMT

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)

Re: Limit Dimension in pivot table for date

Thu, 10 Apr 2014 15:35:18 GMT

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.

Re: Limit Dimension in pivot table for date

its_anandrjs — Thu, 10 Apr 2014 15:41:45 GMT

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

Re: Limit Dimension in pivot table for date

Thu, 10 Apr 2014 15:45:39 GMT

the pity is that I'm not allowed to do any database changes or enhacements. I need to do this pure in qlik.

so again: how to limit dimention of pivot table to show only 10 latest dates from a field that can contain any (discontinuing) date values?

Re: Re: Limit Dimension in pivot table for date

MK_QSL — Thu, 10 Apr 2014 16:08:17 GMT

Check enclosed file....

Re: Re: Limit Dimension in pivot table for date

its_anandrjs — Thu, 10 Apr 2014 16:19:23 GMT

No in qlikview script load i am suggesting.

Re: Limit Dimension in pivot table for date

Thu, 10 Apr 2014 16:31:36 GMT

not true

Re: Limit Dimension in pivot table for date

MK_QSL — Thu, 10 Apr 2014 16:33:20 GMT

Check file I have enclosed..

Re: Limit Dimension in pivot table for date

Thu, 10 Apr 2014 16:41:13 GMT

yeah, its doing its job, but date values becomes corrupted like :

41737.378923611

41738.0865625

41738.314502315

41738.333645833

instead of original:

8.4.2014 8:00

8.4.2014 9:05

9.4.2014 2:04

9.4.2014 7:32

9.4.2014 8:00

9.4.2014 14:55

thank you!

Re: Limit Dimension in pivot table for date

MK_QSL — Thu, 10 Apr 2014 16:58:15 GMT

Try below in Script..

=Date(Date#(YourDateField,'D.M.YYYY h:mm')) as YourDateField

Date(YourDateField)

Date(FLOOR(Date#(YourDateField,'D/M/YYYY h:mm'))) as YourDateField

Re: Limit Dimension in pivot table for date

Thu, 10 Apr 2014 17:57:03 GMT

okey, now I see that dates are not ordered and are not the latest ones... originally I asked for 10 latest dates from field.

Re: Limit Dimension in pivot table for date

MK_QSL — Fri, 11 Apr 2014 06:21:26 GMT

Please check enclosed file..

Re: Limit Dimension in pivot table for date

Fri, 11 Apr 2014 08:18:56 GMT

Thanks for your effort, Manish!
Please, can you explain a bit what is causing ordering here?

in addition, what is the difference between Date() and Date#() functions?

Thank you!