topic Compare figues between years in QlikView

Compare figues between years

Tue, 14 Feb 2012 13:37:25 GMT

Hello,

I have a straight table with Date as dimension and the following expressions

- Sum(Qty)

- aggr( rangesum(above(Sum(Qty),0, rowno())), YearMonth, Datum)
- aggr(rangesum(above(Sum(Qty), 0, rowno())), Year, Datum)
- Sum(ValueExVAT)
- aggr(rangesum(above(Sum(ValueExVAT), 0, rowno())), YearMonth, Datum)
- aggr(rangesum(above(Sum(ValueExVAT), 0, rowno())), Year, Datum)

The result is

DATE Tot Acc per month Acc per year

2012 jan-01 100 100 100

2012 jan-02 120 220 220

2012 jan-03 80 300 300

...

2011 jan-01 110 1300 1300

2011 feb-02 90 90 1390

2011 feb-03 130 220 1610

Now what I want to to is add 4 more expressions that compare the above values between the years.

1 = Number of products sold (called Tot in the exemple above). i.e. values this year / value last year

2 = Value of products sold (this year compared to last year)

3 = Aggregate value for the month compared to last year

4 = Aggregate value for the year compared to last year

I have tried using set analysis, using a set modifier.

sum(Qty) / sum({$<Datum= {"=today()-365"}>}Qty)

But the result is the same value divided by itself e.g. 100/100 = 1

Or the result has been 0

Anyone know how to do this?

I have values between 2010-01-01 -> today

All dates during 2010 will be empty because there is nothing to compare against but for 2011 and 2012 the comparison should be possible.

Martin

Compare figues between years

CELAMBARASAN — Tue, 14 Feb 2012 13:49:32 GMT

Hi,

You can try with the add months function as below.

sum(Qty) / sum({$<Datum= {$(=AddMonths(Datum,-12))}>}Qty)

Hope it helps

Celambarasan

Re: Compare figues between years

v_iyyappan — Tue, 14 Feb 2012 14:00:41 GMT

Hi,

Use expression like this,

AddYears() function used get to previous years and also get into next years.

sum(Qty) / sum({$<Datum= {$(=AddYears(Datum,-1))}>}Qty)

Where Datum = MaKeDate(Max(Year),Max(Month),Max(Day))

Regards,

Iyyappan

Re: Compare figues between years

Tue, 14 Feb 2012 14:17:26 GMT

Try this:
sum(Qty) / sum({$<Datum={"$(=date((today() - 365)))"}>}Qty)

Compare figues between years

Thu, 16 Feb 2012 15:18:50 GMT

Hello,

Hm I have tried your suggestion but I can not get it to work.

I have uploaded an example application.

Can you have a look and see if you can get it to work there?

Martin

Compare figues between years

CELAMBARASAN — Thu, 16 Feb 2012 17:02:10 GMT

Hi,

You can try with this expr as below.

sum(Qty) / sum({$<Datum= {">=$(=MonthStart(Today(),-12))<=$(=MonthEnd(Today(),-12))"}>}Qty)

Hope it helps

Celambarasan

Compare figues between years

Fri, 17 Feb 2012 08:23:33 GMT

Hello,

I still cant get it to work.

I uploaded an application in my first post.

Can you see if you can add an expression where you get it to work?

At the moment I using

sum({$<Datum= {">=$(=MonthStart($(=Datum), -12)) <=$(=MonthEnd($(=Datum), -12))"} > }Qty)

You used today() in you formula but this does not work because then only values that are 12 months old are used. I need the comparison to be applied on all dates.

Martin