topic Re: compare previous months/years data in QlikView

compare previous months/years data

Fri, 27 Nov 2015 04:00:26 GMT

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

Re: compare previous months/years data

Gysbert_Wassenaar — Fri, 27 Nov 2015 16:15:53 GMT

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.

Re: compare previous months/years data

Fri, 27 Nov 2015 17:48:46 GMT

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.

Re: compare previous months/years data

Sun, 29 Nov 2015 02:18:25 GMT

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.

Re: compare previous months/years data

Gysbert_Wassenaar — Sun, 29 Nov 2015 16:18:37 GMT

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

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

Re: compare previous months/years data

Mon, 30 Nov 2015 00:46:26 GMT

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!