compare previous months/years data

[#1]

Hi there,

i Am trying to compare previous years data for this year.

Assuming there are similar data previously, means this extends to say 2010.

Month      Year           Value

1              2013            1

2              2013            2

3              2013             3

.

.

.

.

12            2013              12

1              2014               13

.

.

.

12             2014              24.

Here is what i want to be displayed on a table(i am not sure what table to use):

Month  Year  Ratio

1          2015   Sum of Value of Month 1-3 Year 2014/ Value of Month 1-3 Year 2013

2          2015   Sum of Value of Month 4-6 Year 2014/ Value of Month 4-6 Year 2013

Variable:

vdate=date(makedate(Year,Month),'MM-YYYY')

To Calculate the Numerator part

I used the following: sum({< >=AddMonths($(vdate),-12), <=AddMonths($(vdate),-9)>}Value)

And results are On the same table showing me :

Month      Year           Value           Numerator

1              2013            1                         1<----------------------------------It seems to think i want them to calculate the sum of values here and

2              2013            2                         2<----------------Here and

3              2013             3                        3<---------------------Here

.

.

.

.

12            2013              12                     0

1              2014               13                   0

.

.

.

12             2014              24.                  0 <------------------------------------------ Shouldnt this be Value of Dec 2012 + Value of Jan+Feb 2013?

1              2015                                      0  <---------------------------------------- Shouldnt this be 1+2+3?

.

.

.

12             2015                                   0

[Gysbert_Wassenaar]

sum({< >=AddMonths($(vdate),-12), <=AddMonths($(vdate),-9)>}Value)

The part between {< and >} is not correct. If you're lucky it will simply be ignored. What field value is supposed to be >=AddMonths($(vdate),-12)?

Also, set analysis is calculated at the chart level, not the row level. So if you are using Year as a dimension then a set analysis expression can't calculate with values from 2014 if the dimension value is 2015. You can try using an AsOf table to link the year values to previous years. Something like:

AsOf:

LOAD * INLINE [

ReportYear, Year, YearsAgo

2010, 2010,0

2011, 2010,1

2011,2011,0

...etc

2015, 2014,1

2015, 2015,0

];

You can then use ReportYear as dimension in your table and expressions like Sum({<YearsAgo={0}>}Value) for the value of the current year and Sum({<YearsAgo={0}>}Value) for the previous year.

[#3]

Hi, thank you for the clarification.

1.can the field value be $(vdate)?

2. OH I SEE, no wonder i cant get it to work.

Is there a way around this other than the load inline?

Because i would need to breakdown by months as well, to display 2014 months data on 2015 dates as well.

Thank you.

[#4]

Hi, thank you for the clarification.

1.can the field value be $(vdate)?

2. OH I SEE, no wonder i cant get it to work.

Is there a way around this other than the load inline?

Because i would need to breakdown by months as well, to display 2014 months data on 2015 dates as well.

This means that the ratio i am trying to get, goes over 2 years. for example might run from November 2013, to january 2014.

Thank you.

[Gysbert_Wassenaar]

1. No, idea. I don't know what $(vdate) evaluates to.

2. Perhaps this blog post helps: The As-Of Table

[#6]

Hi, just posted my problem with real data set(qlikview and excel output)

Tricky question (With data and qlikview sheet attached)

Hope you can help ! Thank you!