Year to Date for impromptu

[slaforce]

I need to create a year to date and previous year calculation to add up sales dollars in an Impromptu report. Any suggestions on how to do this?

 

[Kaiaualad]

To do two different years in the same report, you either need to do a subquery for the Previous year,as the Fact Table Year cant do two joins to two different year columns, or put a 0 column in the Fact table and as the primary key of the Year table,so that One join can pick up two separate years.

 

[rptlady]

You might also try using period end date in your filter. You will have to make sure your period_schedule table is joined to the table you are using. This of course is assuming that the table holds fiscal year, period number and sub period number.

 

[DrSimon]

You can do this in one Impromptu report by using calculated fields.

If you are just using the simple calendar year the 2 fields would be:
Year-to-date Sales$=
If (year(Sales_Date)=year(Now())) then (Sales$) else Null

Previous Year Sales$=
If (year(Sales_Date)=year(Now())-1) then (Sales$) else Null

Now() could be replaced by a prompt variable and Null by (0) if necessary.

Once this is done, you can use whatever function you want on the calculated fields.If the years need to be fiscal you will need to use some sort of date difference calculation, but the principle remains the same.

I've used this idea many times to get over the problem of how to get information in one report where incompatible joins or filters would otherwise make that impossible.

Simon Rouse