nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Relative Time Period Aggregation

pauloux

Hi All,

I am trying to use a relative time period aggregation into a CrossTab, but not able to obtain the expected result.

I would like to display monthly the number of "resolved demands" during the last 12 months :

To clarify i attached a quick screen shot of the expected result.

I was unable to find any documentation related to "relative time period aggregation" - Does someone has any example ? Any description of each parameter of the "relative time period builder" ?

I made many tests but there are so many possibilities....

Thx in advance, regards,

Find more posts tagged with

Comments

mwilliams

It looks like the screen shot didn't get attached. Can you please try again?

pauloux

You are wright, it seems that my initial attachment did not work.

Since yesterday, i have made many tests and succeeded in using properly the relative time period aggregation to make what i want.

What i wanted to do: display monthly the number of tickets resolved during last 12 months - Example: for 08/2012 to display the total number of demands resolved from 09/2011 to 08/2012.

To do so i used relative time period aggregation - I used the following parameters (see attachment - I share those parameters hoping that it will help people, cause it was really hard and not clear for me to use properly this functionality):
- Time Period: trailing N Months
- Number of trailing N Months: 12
- Function: SUM
- Expression: measure["NumberOfResolution"] (here we must put measure from datacube & not data from crosstab)
- Aggregate on: Group2/Month (to calculate my aggregation montly on the last 12 months)
- Reference date: "Last date in the period"

Unfortunately, i still have issues...

I want to display 12 months in my crosstab, but to calculate my relative time period aggregation i need 24 months data ( example: if i want to display the period from 09/2012 to 10/2011 in my crosstab: to calculate the number of resolved demands during the last 12 months for the month of 10/2011, i will need data from 11/2011 to 10/2010)- See screen shot for better understanding of the obtained result.

Usually to limit the number of months in a crosstab, i use the filter property of my crosstab. As i need 24 months, i extended the filtered period to 24 months...but as a consequence, 24 months are displayed in my table...i found some ways to hide the columns (using scripts or highlights and the property display:none), but :
- display:none is not working into internet explorer
- my report needs to be exported properly in PDF and with this "workaround" the result is not good(see screen shot).

Sorry for this quite long explanation, hope this is clear enough,

Thx in advance,

mwilliams

What is your BIRT version?

pauloux

Version 4.2, the last one available on eclipse website.

mwilliams

You could create one crosstab with 24 months represented, to get the values, then in another crosstab, you can use the values from the first crosstab to fill the cells. You would hide the first crosstab.

pauloux

Hi,

First, thx once again for your answer.
The idea seems to be very exciting but i tried and did not find the solution...Could you please explain how i can do this ? Or do you have any example ?

Explanation:
- I already have the crosstab with 24 columns
- I try to create a crosstab with data coming from the first one : When i go in the sheet "binding" into my crosstab, in the list "data" i have no other choice than available datacubes.

I know that i can use data from a crosstab into a chart, but i have no idea of how to use crosstab data into a crosstab or something else.

Thx in advance for your answer, regards,

Paul

mwilliams

Here's a simple example:

pauloux

Hi,

Thank you very much for your help, i finally succeeded in doing it! I have the expected result !

I just put my understanding here so that newbies like me will save sometime :
- In the first crosstab on the event "on create" of the data you want to display, you store the value into a "persistent global variable" (data[Month])
- Into the 2nd crosstab you just have to add a row and to display the value of the variable you previously set.

Just for my complete understanding, could you please explain how it works ? Is it possible to use another variable than "data[Month]" ?

mwilliams

The first crosstab is created first. That's why we're able to grab its data and use it in the second. The reason I use the dimension value as the variable name is because it allows you to recall the variable in the proper spot in the second crosstab. Hope this makes sense.

pauloux

Hi,

Sorry to reply so lately but i have been quite overloaded during the past weeks.

I have another question regarding the solution you proposed to me.

In my report i need to use this "workaround" twice - I have :

- a first hidden crosstab in which i store the value on the last 12 months
- a first displayed crosstab in which i use the persistent variable (data[Month]) of this previous hidden table.
- a 2nd hidden crosstab in which i store a 2nd value on the last 12 months
- a 2nd displayed crosstab in which i use the persistent variable (data[Month]) from this 2nd hidden table.

==> My issue is that in the 2 displayed crosstabs i display the same value (coming from the 2nd hidden crosstab). In my mind i thought each crosstab was generated according to its order into the report (from the top to the bottom), but it seems that hidden table are generated before displayed tables ? Am i wrong ?

Do you have any idea ? Any solution / explanation ? Can iuse another variable than data[Month] ?

mwilliams

Can you modify the example I posted to show what you're dealing with? data["Month"] is just the dimension value, to help keep your data in order. If you have different dimensions than just month, of course you can use them.

pauloux

As seen with Michael i created an enhancement demand in bugzilla regarding this issue :

It is available there: https://bugs.eclipse.org/bugs/show_bug.cgi?id=398560

mwilliams

My mistake on having you file a bug report for this. I forgot about the recalculate totals option that you can uncheck on the fiter to make this work without the messy old workaround.