Variance calculation in report Studio

[tradeex]

Hello -

I have a bit of a difficulty calculating variance column without specifying additional data items in the query that are for the specific version that I am calculating my variance. Here is what I need to derive at (the variance column):

version_id-->>>   ACTUAL                   PLAN   VARIANCE
JOE                       10,500,000            11,000,000.      500,000
DAVE                 1,000,000             2,000,000   1,000,000
STEVE                 1,000,000              2,000,000   1,000,000

The issue is with my table structure :

VERSION_ID   MONTH   YEAR   REP   AMOUNT
ACTUAL             JAN   2015   JOE       10,500,000
ACTUAL             JAN   2015   DAVE   1,000,000
ACTUAL             JAN   2015   STEVE   1,000,000
PLAN             JAN   2015   JOE          11,000,000.
PLAN             JAN   2015   DAVE   2,000,000
PLAN             JAN   2015   STEVE   2,000,000

SO the only way I can come up is to add another data item saying
tOTAL(IF( [VERSION ID]='PLAN') THEN ([AMOUNT]) ELSE (0))

but I am trying to see if there is a better way of doing that by just calculating the variances.

Thanks!!

[Lynn]

Given your data structure I think your approach makes sense. I would have a two data items, each with the if statement to populate appropriately for either plan or actual.

I would omit the "total" function and just use the if statement, but set the aggregation function for each to be total. Best thing to do is to check the generated sql to see if the aggregation is happening on the database side (e.g., your sql shows a sum() function and a group by).

An alternative approach is to have two queries (one for plan and one for actual) and then union these together, but that is going to require local processing and may perform poorly depending on the size of each query result set.

The best solution is to have this data modeled in your source to support reporting. This sounds like one fact table that really contains two different facts which is not at all the ideal approach.

[Robl]

What  you have there is a fact table that's not been denormalised.

I'd just be inclined to make two calculated columns (possibly in FM if you can change the model.)
Plan
IF( [VERSION ID]='PLAN') THEN ([AMOUNT]) ELSE (0))
Actual
IF( [VERSION ID]='ACTUAL') THEN ([AMOUNT]) ELSE (0))

Then a third column for
Plan-Actual

If you then group the report by Rep then the grouping and sumarising on the report will take care of the rest.

[tradeex]

If I use the following expression

IF( [VERSION ID]='PLAN') THEN ([AMOUNT]) ELSE (0))
Actual
IF( [VERSION ID]='ACTUAL') THEN ([AMOUNT]) ELSE (0))

I need to calculate month and YTD based on prompt selection. Do I need two dataitems with the ?prompt? in the if statement?

Thanks

[Robl]

Do you mean "Month and YTD" or "Month or YTD".

Either way, you just treat the calculated measure as you would any other measure.
Obviously you need to make sure you have the rollup options correct.

[tradeex]

Yes I need month and YTD.

But based on my prompt for Month and Year, the month measure and YTD needs to be calculated. So if a user selects Feb and 2015 as a prompt, month = Feb and YTD will be feb YTD. How would I do that using a measure and prompt?

[tradeex]

Ok I have month and YTD working but cannot get PY to work correctly. I have a month prompt and a year prompt. When selecting a year prompt, PY should also show for that month. My logic:

tOTAL(IF( [VERSION ID]='ACTUAL' AND [YEAR] = (?YEAR? -1 )) THEN ([AMOUNT]) ELSE (0))

but i get zero. Prompt selection is month = JAN and YEar 2015 so I should see something with the above logic.
Please help.

thanks!

[Robl]

Well, clearly you need to NOT filter on the prompt variable.

Instead, set the filter to be;
year(prompt) = Year(date)

That way you'll get the full YTD values in your dataset.
Create the Variance Column as normal, this will be correct for YTD.

Then create a new column for 'Current Month' which would be
if (month(prompt) = month(date)) then (variance) else (0)

If you want to show PREVIOUS YEAR then of course you need to adjust your filter again so it's;
year(prompt) >= Year(date)-1
so your dataset includes last year and this year.

Then you'll need to alter your calculation in the columns to select the current year as well as the current month.
I'm not writing that out, because it's blindingly obvious how to do it.

[tradeex]

Sorry a bit confused. So I don't need a year prompt? the user wants me to use a year prompt so how do I get to show PY where my year prompt or a filed is just a YEAR. I don't have any dates in my table.
Thanks