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; } } }

Year-by-Year comparism

marcs

Hi guys,
 
first of all: this forum is great. I'm new to Birt but learned alot here :-)
But one task I have at hand I cannot find a solution yet although I think it's not uncommon:
 
I want to compare agregated sales numbers to the numbers of previous year.
 
Example:
I have a db table with the following columns columns:
DateOfOrder | NumberOfSoldItems | Platform
 
With that I made a crosstab so I can see the summarized NumberOfItems sold per Month and Platform.
 
I now want to compare these NumberOfItems to the NumberOfItems of the same month of the last year and show the difference in the table cell.
 
The user sets two report parameters: begindate and enddate
 
Ii already tried to make 2 date sources with 2 their data sets: one giving the results of the sales within chosen begindate and enddate and another data set with the same parameters minus one year.
 
Then I tried to combine these two datasets into one but I don't know at which level I can access the two NumberOfItems dates to compare/substract them.
 
If someone could point me into the right direction it would be great, I'm trying and searching since days now...
 
Thank you in advance!
Marc

Find more posts tagged with

Comments

pricher

Hi,
 
You should be able to do this by using Relative Time Periods in a cross tab. You can find the Relative Time Periods component in the palette. Drag one in the summary area of a cross tab and choose the appropriate Time Period. Each time period you add becomes a data binding that you can use later in a calculation.
 
I am sending you an example based on Classic Models that shows in the crosstab the difference between current year and previous year for each country. Hopefully, this will help you build your solution.
 
P.

marcs

Hi Mr. P. ;-)
 
thanks a lot! This is exaclty what I needed. Some thingsin live are easy :-)
 
best regards
Marc

kumbare

Hi pricher,
 
Is there any expression to find difference between two years
 
such as data["currentYear"]-data["previousYear"].
 
Thanks in advance.

pricher

Hi,
 
Simply choose Previous N Year and Current Year in the Relative Time Period dialog, as shown here:

kumbare

Hi Pricher,
 
Thank you for quick response. I am new to BIRT.
 
is it possible for you to implement same in your <a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?app=core&module=attach&section=attach&attach_id=10917' title="Download attachment">YearOverYear.rptdesign</a> ?
 
Ref thread ( <a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?/topic/36961-year-vs-year-1-comparison-in-birt-cross-tab/?p=137921'>http://developer.actuate.com/community/forum/index.php?/topic/36961-year-vs-year-1-comparison-in-birt-cross-tab/?p=137921</a>.
 
Please help.
 
Thanks in advance.

pricher

Hi,
 
Attached is an example that shows Year over Year difference. The YoY value is a computed value where PY is subtracted from Current Year. Also, because there cannot be a YoY for the first year of data, I have added a visibility rule to hide the field when Year = 2011.
 
Hope this helps,
 
P.

kumbare

Hi Pricher, 
 
Thanks a lot for your help. It worked for me. 
 
I am facing another issue in same report. Please refer below screen shot. 
 
Diff is derived column in cross tab so i not able to calculate sub totals and grand totals of the Diff column. 
 
Please help me to calculate derived columns subtotal in cross tab.

Year 2011 YoY 2012 YoY Diff 
COUNTRY States 
Australia 
 a1 2514 100 2414 
 a2 872 200 672 
 a3 47 300 -253 
 a4 500 400 100 
 a5 903 500 403 
Sub totals 4836 1500 3336 
Belgium 
 b1 5400 100 5300 
 b2 1200 200 1000 
 b3 1300 300 1000 
 b4 100 400 -300 
 b5 5600 500 5100 
Sub totals 13600 1500 12100 
Total 18436 3000 15436

pricher

Hi,
 
You have to calculate the sub and grand totals individually for relative time periods and derived measures. Take a look at the attached example.
 
 
P.

kumbare

Thank you Pierre.
 
Could you please elaborate this solution ?
 
Have you written any script for calculation ?

pricher

Here are the general steps:
 
1. Start with a basic Data Cube like this one:

kumbare

Thanks a lot Pierre for detailed explanation.
 
I will apply it in my cross tab report.

kumbare

Hi Pierre,
 
Is there any way to hide cross tab columns say year difference column ?
 
How to hide a particular column in cross tab ?

pricher

Best way is to use the Visibility attribute on the element(s) you want to hide. For example, to hide the YoY values for 2011, check the Hide Element property in the Visibility attribute and apply the condition data["Year"] == 2011, as shown below:

kumbare

Hi Pierre,
 
Thank you for this solution.
 
I am not using grid in cross tab so it is hiding element but not the blank column. it is showing blank column
 
Is there any other way which can always hide 4th number of column in cross tab irrespective of the value in column ?
 
Can we write any script for this ?

pricher

Hi,
 
Sorry for the late reply... Can you share an example or a screen shot of what you are trying to accomplish?
 
Thanks,
 
P.

kumbare

Hi,
 
We can take any cross tab example <a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?app=core&module=attach&section=attach&attach_id=11980' title="Download attachment">lets take example of YearOverYear4.rptdesign</a> shared earlier by you.
 
I want to hide column D in excel irrespective of its value.

pricher

Hi,
 
Instead of applying the Visibility attribute to the element, you can apply it to the whole grid column. The attached sample design does that and when you export to Excel, the whole column disappears.
 
P.

kumbare

Hi ,
 
I am using Relative time period in Cross tabs to compare sales report with next year.
 
When I run it in eclipse (Run-View Report -as xlsx) It is calculating decrease in revenue properly but when am running it through java application, formula applied on these relative time periods are not getting properly calculated.
 
I am creating one war for BIrt report and we are creating dynamic url to hit that war file from java application.
 
Is there anything that i need provide in to URL?
 
Please help.

pricher

Hi,
 
That would be out of my scope. Maybe someone else can chime in.
 
P.

kumbare

Hi Pierre,
 
Thank you for quick reply.
 
Can you please confirm that "Relative time period" is also available in lower versions of BIRT i.e. <4x ?

pricher

Relative Time Periods were introduced in <a data-ipb='nomediaparse' href='https://eclipse.org/birt/phoenix/project/notable4.2.php'>BIRT4.2</a>

kumbare

Hi Pierre, 
 
Thank you Pierre. 
 
Everything looks good after changing BIRT version. 
 
I want to change sequence of the comparison columns and move these columns at the end of year column. in current design we are showing these column next to each year. 
 
is it possible to show all the comparison columns at the end of the year column using cross tab design ? 
 
Please help.

pricher

Not really doable.
 
In a crosstab, the relative time period fields are generated dynamically and their number will vary depending on the number of columns generated by the dimension going across, in your case Year. Hence, you cannot have them all at once at the end of each row. If your requirements are similar to the screenshot you posted, i.e. show the data for 3 years only, a better solution might be to use a regular table and compute your relative time periods by creating aggregates for each year by city and then create bindings that calculate the difference between the years. I have attached a sample of what it might look like.
 
Hope this helps,
 
P.

kumbare

Hi Pierre,
 
Thank you for your help.
 
I want to migrate from BIRT 2.6 to 4.2. I have war created for older version.
 
Could you please help me to migrate this war file to 4.2 ?
 
What are the required changes to migrate this war file to 4.2 ?

pricher

Hi,
 
I am not an expert on this. I would strongly suggest you start a new thread on the Integrating with BIRT Runtime forum to get help.
 
Regards,
 
P.

kumbare

Hi,
 
Thank you for your help.
 
There is something wrong with relative time period function. I am getting below year if I am setting Next yer relative period Reference to Today.
 
I guess due to this reference field only 'relative time period function' for Next year calculation is not working in my application.
 
 
Caused by: java.lang.ArrayIndexOutOfBoundsException 
at java.lang.System.arraycopy(Native Method) 
at org.eclipse.birt.data.engine.olap.data.impl.aggregation.TimeFunctionCalculator.getAggregationResultSet(TimeFunctionCalculator.java:523) 
at org.eclipse.birt.data.engine.olap.data.impl.aggregation.AggregationExecutor.execute(AggregationExecutor.java:337) 
at org.eclipse.birt.data.engine.olap.data.api.CubeQueryExecutorHelper.onePassExecute(CubeQueryExecutorHelper.java:944) 
at org.eclipse.birt.data.engine.olap.data.api.CubeQueryExecutorHelper.executeFactTableQuery(CubeQueryExecutorHelper.java:484) 
at org.eclipse.birt.data.engine.olap.data.api.CubeQueryExecutorHelper.execute(CubeQueryExecutorHelper.java:417) 
at org.eclipse.birt.data.engine.olap.query.view.QueryExecutor.executeQuery(QueryExecutor.java:951) 
at org.eclipse.birt.data.engine.olap.query.view.QueryExecutor.populateRs(QueryExecutor.java:904) 
at org.eclipse.birt.data.engine.olap.query.view.QueryExecutor.execute(QueryExecutor.java:183) 
at org.eclipse.birt.data.engine.olap.query.view.BirtCubeView.createCubeCursor(BirtCubeView.java:201) 
at org.eclipse.birt.data.engine.olap.query.view.BirtCubeView.getCubeCursor(BirtCubeView.java:182) 
at org.eclipse.birt.data.engine.olap.impl.query.CubeQueryResults.createCursor(CubeQueryResults.java:298) 
at org.eclipse.birt.data.engine.olap.impl.query.CubeQueryResults.getCubeCursor(CubeQueryResults.java:161) 
at org.eclipse.birt.report.engine.data.dte.CubeResultSet.<init>(CubeResultSet.java:78) 
at org.eclipse.birt.report.engine.data.dte.DteDataEngine.doExecuteCube(DteDataEngine.java:249) 
at org.eclipse.birt.report.engine.data.dte.AbstractDataEngine.execute(AbstractDataEngine.java:280) 
at org.eclipse.birt.report.engine.executor.ExecutorManager$ExecutorContext.executeQuery(ExecutorManager.java:447) 
at org.eclipse.birt.report.item.crosstab.core.re.executor.BaseCrosstabExecutor.executeQuery(BaseCrosstabExecutor.java:122) 
at org.eclipse.birt.report.item.crosstab.core.re.executor.CrosstabReportItemExecutor.execute(CrosstabReportItemExecutor.java:102) 
at org.eclipse.birt.report.engine.executor.ExtendedItemExecutor.execute(ExtendedItemExecutor.java:62) 
at org.eclipse.birt.report.engine.internal.executor.dup.SuppressDuplicateItemExecutor.execute(SuppressDuplicateItemExecutor.java:43) 
at org.eclipse.birt.report.engine.internal.executor.wrap.WrappedReportItemExecutor.execute(WrappedReportItemExecutor.java:46) 
at org.eclipse.birt.report.engine.internal.executor.l18n.LocalizedReportItemExecutor.execute(LocalizedReportItemExecutor.java:34) 
at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:65) 
at org.eclipse.birt.report.engine.layout.html.HTMLPageLM.layout(HTMLPageLM.java:92) 
at org.eclipse.birt.report.engine.layout.html.HTMLReportLayoutEngine.layout(HTMLReportLayoutEngine.java:100) 
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.doRun(RunAndRenderTask.java:181)

pricher

Hi,
 
Can you send your report?
 
P.

kumbare

Hi Pierre,
 
Sorry but it is very big report and will require DS.
 
I am getting this error when i am using Next N year feature of the 'Relative Time Period' but if i change it to Previous N Year it is working fine.
 
Problem with using Previous N Year is that i am not able to hide empty columns in cross tab for first year. I can not use grid as it dues not look good.
 
Could you please help me to completely remove cells from excel sheet using script ?
 
Or if i will be using Grid in Cross Tab then will these grid cell will appear separate cells in excel ?
 
 
Please help me.

pricher

As I explained earlier in this thread, crosstabs can be a bit hard to work with as they tend to be a bit rigid. Sometimes, based on the requirements, it is easier to use a table, as I have shown in YearOverYear5.rptdesign sent earlier.
 
Without your report design, it will be very hard to come up with a solution. Plus I don't understand how using Next N period as a relative period will give you the correct results since Year over Year typically compares this year with the previous year, not the next.
 
P.