How to compare months

[#1]

Hi everybody,

I have a table with information since Jan 2008, and I want to compare the sales information between months, that means comparisson between each month with same month the year before

for example

Sales (Jan 2009) / Sales (Jan 2008)

Sales (Feb 2009) / Sales (Feb 2008)

Sales (Apr 2009) / Sales (Apr 2008)

I hope you can help me

Regards

[#2]

the problem is that in the example you have published you use quarte values, and I need months values

I'm so sorry but I am new on QV and I'm trying changing this complex thing, that's why I didn't get your solution Idea,

Could you please explain it better? or try to use it in the example you have sent,

Regards

[pover · Accepted solution]

Angel,

You are correct that with the MonthYear dimension my formula doesn't work. You have to use Month as the dimension. See the attached example.

You have 2 other options. Use the before() function like Angel mentioned which is a decent solution or in the worse case situation use a dimension monthyear from a table island.

Regards.

[#4]

Hi,

See this script:

TRIMESTRE_PRODUCTO:
Load
AUTONUMBER(PERIODOS & '-' & MERCADO & '-' & PRODUCTO & '-' & COD_BRICK) AS PMPB,
TRIMESTRE_PRODUCTO,
TRIMESTRE_PRODUCTO_KILOS;
SQL SELECT
"co_producto_pfz" AS MERCADO,
"co_brick" AS COD_BRICK,
"co_producto" as PRODUCTO,
('01/' + periodo) AS PERIODOS,
"promedio_trimestre" AS TRIMESTRE_PRODUCTO,
"promedio_trimestre_kilos" as TRIMESTRE_PRODUCTO_KILOS
FROM "DDD_TMP2" WHERE substring(periodo,4,4) >='2007';

Your script just return the values of TRIMESTRE (QUARTER) direct on yhour Data Base (fields promedio_trimestre and promedio_trimestre_kilos).

If you want the month values, you need to return from your data base.

I don't know how to help you in this case, because i don't know how to work your DB.

Sorry

[#5]

Eduardo you are right,

But what I'm talking about is not to use the Quarte value ("TRIMESTRE") and to use ("UNIDADES") instead of that,

But the problem is how to sumarize two diferents months and get the diferences between them or in other words "growth"

growth in this month is represented by this formula:

(Units Values (octuber 2010) / Units Values (octuber 2009)) - 1

[#6]

Hi,

So this expression are you want ok

"Sum(IF(VISTA='UNIDADES',UNIDADES,KILOS))"

in your char the expression is

"IF(NUM(Month(PERIODOS), '00') = BEFORE(NUM(Month(PERIODOS), '00')) AND (Year(PERIODOS) - 1) = BEFORE(Year(PERIODOS)),
BEFORE(Sum(IF(VISTA='UNIDADES',UNIDADES,KILOS))) / Sum(IF(VISTA='UNIDADES',UNIDADES,KILOS)) , 0)"

The function Before return the last line, in this case '01/2009' and the current row '01/2010'.

In the some line you have two values (01/2009 and 01/2010) because the inter-records function.

[#7]

Ok friends,

Thank you so much , two solutions were almost the same and closed to what I was hopping

Your help was so useful

I really appreciated

Regards