Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
For the object Product Line Total, I would like to calculate the variance between the months for the value Extension for both "R" and "F", but am struggling to figure it out. I have attached the file. As always any assistance is greatly appreciated.
Assuming you always want [Product Type] R and F, and the variance between them:
1. Drop the dimension [Product Type]
2. Create three expressions and be explicit using SET analysis to get what you need.
Expression for R:
=sum
(
{<
[Product Type] = {'R'}
>}
Extension
)
Expression for F:
=sum
(
{<
[Product Type] = {'F'}
>}
Extension
)
And, the variance:
=sum
(
{<
[Product Type] = {'R'}
>}
Extension
)
-
sum
(
{<
[Product Type] = {'F'}
>}
Extension
)
....
That is just one way.......
Hope it helps.
Sam,
Thanks for the feedback, but I would like to get the variance of "F" between the month 1 and month 2, and of "R" between month 1 and month 2 not the difference betweet F and R.
So, you want R vs. prior month, F vs. prior month?
There are a few ways, but let's take a direct approach.
Modify your load script such that you have, on every row, a [Prior Month]
That is done via addmonths([Month],-1) AS [Prior Month]
Then, to get the prior month value of F, in your expression, use something like this:
=sum
(
{<
[Product Type] = {'F'},
Month = [Prior Month]
>}
Extension
)