topic Re: Set analysis expression for 13 month trend. in QlikView

Set analysis expression for 13 month trend.

Sun, 20 Jan 2013 11:03:30 GMT

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.

Re: Set analysis expression for 13 month trend.

lironbaram — Sun, 20 Jan 2013 12:40:21 GMT

hi attach is an example

hope it helps you

Re: Set analysis expression for 13 month trend.

Mon, 21 Jan 2013 12:02:12 GMT

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!

Re: Set analysis expression for 13 month trend.

lironbaram — Wed, 23 Jan 2013 09:03:57 GMT

hi attach isnew version

hope it helps you

Re: Set analysis expression for 13 month trend.

Thu, 24 Jan 2013 10:15:44 GMT

Thank you! That worked !!