none
Matrix: percentage growth same period previous year

    Question

  • Not sure if I'm at the right subforum but here's my question.

    I'm trying to creating the following matrix (with more product categories than shown here)

      (still can't post images, so trying to make it clear this way)


                                    Months

                                    Jan | Feb | March | April ...

    Product | Year |

    A | 2014 | 100 | 150 | 150 | 130 | 140

    A | 2015 | 80 | 180 | 145 | 130 | 145

    Variance | 80% | 120% | 97% | 100% | 104%

    B | 2014 |....

    B | 2015 | ...

    Variance | ....

    The matrix in the report builder looks like this:

    ||[Product]  |[Year]   | [sum(Amount)]
    ||                |               <<Expr>>


    The expression:

    =IIF(IsNothing(Previous(Sum(Fields!Amount.Value))) or Fields!Year.Value=First(Fields!Year.Value,"Product"),nothing,(Sum(Fields!Amount.Value)-Previous(Sum(Fields!Amount.Value)))/IIF(IsNothing(Previous(sum(Fields!Amount.Value))),1,Previous(Sum(Fields!Amount.Value))))

    As Row Groups in the matrix I've got Product; Year
    As Column Groups i've got Month

    I can't get it to work. Previous doesn't seem to work in this case (only when I got a total of each year (e.g. one column instead of 12))

    Can someone help me out?

    I really need a solution for this, as I need to create this type of matrix more often.

    When I'm able to insert images, I will insert them to clarify it more.


    Thursday, May 07, 2015 6:42 AM

Answers

All replies