Set analysis expression for 13 month trend.

[#1]

Hi,

I need to write a set analysis expression for the below condition.

Difference of Amount between current month and previous month for their respective bands. And this difference has to be calculated across 13 months.

Lets say, I have data from Nov 2011 to Nov 2012. When I select a particular month(say Aug 2012), the expression should return difference in Amount for Aug 2012(current month) and july 2012(previous month). And when no specific selections are made, I should get a graph for all 13 months showing differences of amount respective to their previous month.

I started out writing this expression, but it would give result only for Nov 2012 and Oct 2012(since that is the current and previous month in my data).

How do i get it for the remaining months as well?

sum({<[Month Year] = {$(=Addmonths(max(MonthYear),0))}, Band ={'A'}>}Amount) - sum(({<[Month Year] = {$(=Addmonths(max(MonthYear),-1))}, Band ={'B'}>}Amount).

Note: Month Year is in MMM-YYYY format.

Any help would be appreciated.

Thanks!

RK.

[lironbaram · Accepted solution]

hi attach is an example

hope it helps you

[#3]

Hey Iiron baram,

Thanks a lot! That is what I was looking for. I might need some further help though.

1. I got around the logic but would be great if you could explain in detail.

2. There is an extra value for Month in x-axis that is showing up. After 13 months,it shows random value for amount  and month as ' - ' . Can you explain that?

3. What if I want to find % of Month on Month trend i.e.,

(current month Amount - previous month amount)/ previous month amount

Thanks!

[lironbaram]

hi attach isnew version

hope it helps you

[#5]

Thank you! That worked !!