You are not logged in.

Date Range

Dear visitor, welcome to Palo Community Forum. If this is your first visit here, please read the Help. It explains in detail how this page works. To use all features of this page, you should consider registering. Please use the registration form, to register here or read more information about the registration process. If you are already registered, please login here.

rst_epos

Professional

  • "rst_epos" is male
  • "rst_epos" started this thread

Posts: 23

Date of registration: Aug 20th 2010

  • Send private message

1

Monday, October 11th 2010, 6:14pm

Date Range

hi guys

can i display data between two date ranges? completely stuck with this

thanks

realquo

Sage

  • "realquo" is male

Posts: 255

Date of registration: Mar 11th 2009

Location: Italy

Occupation: BI Consultant

  • Send private message

2

Monday, October 11th 2010, 7:24pm

RE: Date Range

Hi,
sounds like a SQL statement "WHERE Date BETWEEN xxx and yyy"..
I do not have a smart solution, since this would ideally mean putting multiple Time dimension members (days, weeks, months... it depends on how the Time dimension is configured) in filter, and Palo does not allow this (multiple elements in filter are instead now common to Pivot tables too... :( ).
You could maybe generate some specific elements in the time dimension (e.g. jan to feb, jan to mar, etc) using consolidations of leaf members, but I don't know how much this seems practical to you, e.g. (cons factor between [])

Time
2008
---Jan 08 [1]
---... [1]
---Dec 08 [1]
---FromToAnalysis 2008 [0]
----Jan2008To [0]
-----Jan2008ToFeb [0]
-------Jan2008 [1]
-------Feb2008 [1]
-----Jan2008ToMar [0]
-------Jan2008 [1]
-------Feb2008 [1]
-------Mar2008 [1]
....
etc

If you have separated Year and Month dimensions, you can avoid the year qualifier there. Quite big work anyway but it would work natively.

If someone has a smart solution please let me know!

Regards,
RQ

This post has been edited 1 times, last edit by "realquo" (Oct 11th 2010, 7:25pm)

rst_epos

Professional

  • "rst_epos" is male
  • "rst_epos" started this thread

Posts: 23

Date of registration: Aug 20th 2010

  • Send private message

3

Wednesday, October 13th 2010, 4:26pm

thanks for the reply - i thought about preconfiguring time dimensions for the comparison but i would need something a bit more flexible

seems really strange that palo cant calculate the total between two cube values though :(

tish1

Sage

Posts: 761

Date of registration: Jul 13th 2009

Location: Vienna / Austria

Occupation: Senior Consultant @ Vector SW DV GmbH

  • Send private message

4

Wednesday, October 13th 2010, 9:44pm

Hi,

Palo does only what you tell it to do. You can try to write a rule in a cube with two identical time dimensions. This cube will not contain any data, but reference your cube to calculate your values based on prepared aggregated periodes.

Time Dim in base cube (can be built via ETL Server)

201002_LTD
- 201002
- 201001_LTD
-- 201001
-- 200912_LTD
--- ...
--- ...
The structure of the dimension will be easier and more efficient if you limit the comparison to a certain level like YearTD instead of LifeTD (in ETL Server "to next" instead of "to root").

Two Time Dims in reporting cube (can be built via ETL Server as well):
2009
- 200901
- 200902
- ...
- 200912
2010
- 201001
- ...

If you use variabes for start and end periode you can make sure to get a set of dimensions that's completly in sync.

A rule like this one:

Source code

 
1

['MyMeasure'] = Palo.Data("DB", "BaseCube", concat(!'TimeDim2',"_LTD"), !'Measure') - Palo.Data("DB", "BaseCube", concat(!'TimeDim1',"_LTD"), !'Measure')


The rule should work on C and N Elements and should therefore be quite fast.

Btw. Built-In Time Intelligence is on Jedox' agenda, although it won't be available in 3.2.

Regards.
Robert Tischler
Senior Consultant Vector SW DV GmbH

This post has been edited 2 times, last edit by "tish1" (Oct 13th 2010, 9:48pm)

Etan

Intermediate

  • "Etan" is male

Posts: 15

Date of registration: Apr 16th 2010

  • Send private message

5

Wednesday, October 13th 2010, 10:15pm

Hi all,

I solved this problem like this:

I use 3 dimensions: 1. month with attr. Prev Month, 2. year 3. value

The Value dimension consists of value and consolidated value.

The consolitation is calculated by rule. Like the solution of Janet:
http://www.jedox.com/community/palo-foru…p?threadid=2304

If you want to calculate the value between 2 Dates you can do it like this in frontend: Date 2 cum - Date 1 cum. or Date 2 cum + Prev Year - Date 1 cum or in one formula:

If Year(Date 1) = Year (Date2) then Date 2 cum - Date 1 cum else Date 2 cum + Prev Year - Date 1 cum

I hope this help you.

Regards Etan

tish1

Sage

Posts: 761

Date of registration: Jul 13th 2009

Location: Vienna / Austria

Occupation: Senior Consultant @ Vector SW DV GmbH

  • Send private message

6

Thursday, October 14th 2010, 7:25am

Hi,

consolidating periods through hierarchies should be a lot more efficient than calculating via rule. With the capabilities of ETL Server since the introduction of the Calendar extract, there are little reasons not to create a time dimension with the relevant consolidations.

regards.
Robert Tischler
Senior Consultant Vector SW DV GmbH

This post has been edited 1 times, last edit by "tish1" (Oct 14th 2010, 7:25am)

rst_epos

Professional

  • "rst_epos" is male
  • "rst_epos" started this thread

Posts: 23

Date of registration: Aug 20th 2010

  • Send private message

7

Wednesday, November 3rd 2010, 12:17pm

Quoted

Originally posted by tish1


Time Dim in base cube (can be built via ETL Server)

201002_LTD
- 201002
- 201001_LTD
-- 201001
-- 200912_LTD
--- ...
--- ...
.


can someone help me find out how to do this via the etl? would take forever manually :(

tish1

Sage

Posts: 761

Date of registration: Jul 13th 2009

Location: Vienna / Austria

Occupation: Senior Consultant @ Vector SW DV GmbH

  • Send private message

8

Wednesday, November 3rd 2010, 6:52pm

Hi,

try something like this:

Extract 1:

Source code

 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

<extract name="Datum_Monat_Calendar_E" type="Calendar">
  <comment>
<![CDATA[Monate unter Jahr]]>
  </comment>
  <options>
    <root>Datum</root>
    <language>de</language>
  </options>
  <levels>
    <years>
      <start>${iJahrVon}</start>
      <end>${iJahrBis}</end>
      <pattern>yyyy</pattern>
    </years>
    <months>
      <pattern>yyyyMM</pattern>
    </months>
  </levels>
</extract>


Extract 2:

Source code

 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

<extract name="Datum_YTD_Calendar_E" type="Calendar">
  <comment>
<![CDATA[Monate mit Kumulierung auf Jahr]]>
  </comment>
  <options>
    <root>Datum_YTD</root>
    <language>de</language>
    <TTDmode>toNext</TTDmode>
  </options>
  <levels>
    <years>
      <start>${iJahrVon}</start>
      <end>${iJahrBis}</end>
      <pattern>yyyy'_YTD'</pattern>
    </years>
    <months>
      <pattern>yyyyMM</pattern>
      <timetodate>
        <pattern>yyyyMM'_YTD'</pattern>
      </timetodate>
      <attributes>
        <attribute name="Mon">
          <pattern>MMM</pattern>
          <language>de</language>
        </attribute>
      </attributes>
    </months>
  </levels>
</extract>


Tree Join

Source code

 
1
2
3
4
5
6

<transform name="Datum_TreeJoin_T" type="TreeJoin">
  <sources>
    <source nameref="Datum_Monat_Calendar_E" />
    <source nameref="Datum_YTD_Calendar_E" />
  </sources>
</transform>


Pretty self-explaning I think.

Regards.
Robert Tischler
Senior Consultant Vector SW DV GmbH

rst_epos

Professional

  • "rst_epos" is male
  • "rst_epos" started this thread

Posts: 23

Date of registration: Aug 20th 2010

  • Send private message

9

Thursday, November 4th 2010, 12:33pm

Hi Robert

thanks for the reply, i had no idea what to do with the code but reading through it helped me to figure out what to do through the ETL web interface in the CE so thanks again,

cheers

tish1

Sage

Posts: 761

Date of registration: Jul 13th 2009

Location: Vienna / Austria

Occupation: Senior Consultant @ Vector SW DV GmbH

  • Send private message

10

Thursday, November 4th 2010, 9:49pm

Hi,

with Ctrl+Shift+DoubleClick you can open the XML-fragment view of most ETL Server elements.

Regards.
Robert Tischler
Senior Consultant Vector SW DV GmbH
Rate this thread