Hi
i have the following data. The field "Period#" is the targeted result of this topic
Date Value NewPeriod "Period#"
01-Jan-08 10 1 1
04-Jan-08 10 0 2
09-Jan-08 10 0 2
15-Feb-08 10 1 3
20-Feb-08 10 1 4
The 5 Dates represent 4 Periods as 4th and 9th Jan are the same Period.
In Report Studio I want to establisch a KEY-field called "Period#" as shown above.
This Allows me to to group on this field and SUM the Values.
In Excel I can point a formula to a Previous Record, is there are way to do that in Report Studio?
What other options are there?
Thanks,
Bogdan
What is it you are trying to accomplish?
Assuming you want to do some calculation requiring previous period values, then no, there is no function which allows you to directly reference "previous record" in a result set.
However, there are other ways to do that sort of thing. For example, you can aggregate by period in a child tabular model, then join to the prior period with a filter:
[period#]=[child model].[period#]+1
This approach is limited since it will only return records with corresponding records in a prior period, and the joining is taking place locally which can be a performance issue. You can use a Tabular Set union in another approach, post back with more specifics about your goal if you want more details.
I'm bulding a report with Absense Quotes. There is a legal rule that if the number of days between two periods (read Dates) are within 4 weeks they needs to be threated a one single period.
Using "Running-Difference" I can determine the daysbetween.
Now, I want to establish a unique Period-Number for the periods, so I can use that later to sum & group the Values
Let's redo my example and use 5 days as treshhold.
Date Value daysbetween "Period#"
01-Jan-08 10 0 1
04-Jan-08 10 3 1
09-Jan-08 10 5 1
15-Feb-08 10 37 2
20-Feb-08 10 5 2
I hope this helps to understand.
Wow...that's a good one. So, you are trying to calculate a dynamic, incrementing period number, not join on an already-there period number...
Off the top of my head: if you can manipulate your daysbetween item so that it returns a binary result (0=same period, 1=new period) you can then running-total that result to come up with period numbers; or, similarly, binary "any value" vs. null and use running-count instead. Ultimately you might be able to mash all the functions into a single data item. Then do the period aggregation in a higher-level tabular model.
Very nice suggestion.
I shown in the previous example, I have already a field representing the new-period 1 or 0.
I will let you know the outcome asap. ;)
It worked. You need to three Formulas in total,
1.) running-difference to determine time-gaps
2.) IF-THEN-ELSE to determin in NEW (1) / SAME (0) Period
3.) running-total to sum the result of 2
Glad to hear it.