Compare figues between years

[#1]

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.

br

Martin

[CELAMBARASAN]

Hi,

     You can try with the add months function as below. 

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

Hope it helps

Celambarasan

[v_iyyappan]

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

[#4]

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

[#5]

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?

br

Martin

[CELAMBARASAN]

Hi,

     You can try with this expr as below.

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

Hope it helps

Celambarasan

[#7]

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.

Br

Martin