Compare column based on two different time periods

[katerina00]

I am having trouble with the following:

I want to produce a report and compare an amount field with the sum based on two different dates.  I have a date field in the same table as my Paid Losses field in this example

Example:

Two Parameters:
Date One:
Date Two:

Columns:
Paid Losses Summed Up to Parameter 1 Date
Paid Losses Summed Up to Parameter 2 Date

Can I have two columns in my report based on two different dates?

[sir_jeroen]

Could you describe it in terms of the go sales and retailers framework package?

[katerina00]

I don't have that database installed - but don't let "Paid Losses" throw you off.  Paid Losses is just some amount field.  Like Sales or Revenue.

If I use the Sales example I would have the following:

Sales Column 1, Sales Column 2

Now I need sales aggregated up to Parameter Date 1 and placed in the Sales Column 1 and then I need sales aggregated up to Parameter Date 2.

[sir_jeroen]

Ok I'll try to see what I can do...

[JoeBass]

You can do this by creating a query with two child tabular models.

In the first model, filter the query subject results by one of your date parameters. Ã, Add the summing calculation and a field that will be in common between the two child tabular models to build a join.

In the second model, filter by the other date range, add the calculation and the common field. Ã, 

In the parent's tabular model, join the two children models by the common field. Ã, e.g. Ã, [LeftTM.EmployeeNo] = [RightTM.EmployeeNo].

Drag the fields up from the children into the parent's cube.

[wink]

Hi,

Why not use the simple 'if then else' construction on this one......

So you have date and measure.... create 2 calculations like  'If date < prompt Date 1  then measure else 0' and one for the second prompt date.... as ReportNet auto aggregates the measure values will be calculated as totals till prompt dates specified.....

Regards, vincent

[JoeBass]

One drawback might be that the report will generate at least two rows.

Also, since the date is required in the tabular model to build the calculation, how do you get the totals in just two rows?  Won't a row be generated for each date encountered in the data set?

[wink]

Hi,

Be aware that these are detailed calculations which do not have to be included in the report itself....so that cannot be a problem.

Second, make sure you specify the aggregate properties for these calculations.... so thar they will be totalled automatically for you so you will not have a row per databaserow but a row per tabular model row.....

Regards, vincent

[JoeBass]

I'm not sure what you mean by saying that the calculations don't have to be included. 

Are you implying that the two calculations you mentioned can be included on one line?  Can you explain? 

How the aggregate properties should be set?  When I said a row for each date encountered in the data set, I was referring to each row in the tabular model.

I've had to do a lot of reports where two date ranges are required in the report so that comparisons can be made.  e.g.  In one row, display the total sales of item X in 2004, the total sales of item X in 2003, and a column that shows the difference.  Although I find the child tabular model method to be efficient, it would be great to learn an even quicker method of creating this type of report.

[bdybldr]

Katarina,
Did you get the solution to this issue?  I would use a tablular set, which works much like a UNION query.  Place two tab models inside the tab set.

Paid Losses for Date Parameter1:

select [fieldname] from.. where date = ?Pdate1?

UNION

Paid Losses for Date Parameter 2:

select [fieldname] from... where date = ?Pdate2?

Then set the aggregate to total.


Another option is use the IF..then..else construct:

PaidLosses1:
Total(IF (date =?pdate1) Then ([fieldname]) ELSE (0))

I don't have access to ReportNet at the moment so I can't test this, but I think it will work.