current year vs last year variance (dynamic)

[chris_c]

This would be one of those situations where a Report Calculated Universe Object sure would come in handy. But that does not exist yet.

The only suggestion I can offer that that you'll have to add the LY column to your fact table to get the TY and LY in the same context.

[Mark P]

That said, if you want rolling 12 months versus rolling previous twelve months, it just makes your Last Year and This Year objects more complicated and you need to compare them to sales date.

It's all in the planning - build separate objects for what you need (including the @Prompt driven object) and put them together in the case statements.

[selvaips]

[selvaips]

work around:

1. Create a variable using RowIndex() - This will give you unique id for each of the row in the report. If report returns 100 rows, then it will be 1 to 100 (If you have null values, use either RunningCount("A") - A would be some dummy variable)

2. Capture the UserResponse() of Top N

3. Sort the column which you want to Rank in desecending order

3. Add one more column in the report and use the formula like this - If useresponse() <= RowIndex() then 1 else 0. this will give you the value 1 or 0. In this case the first 5 rows will have value 1 and rest of the 95 rows will have 0 - Capture this in a variable.

4. Add a report filter and drag he variable and say =1. This will display the report which has value 1 always. This would return only first 5 rows.

[Mark P]

[Mak 1]