topic How to compare months in QlikView

How to compare months

Wed, 13 Oct 2010 17:29:36 GMT

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

How to compare months

Wed, 13 Oct 2010 20:23:29 GMT

Hi,

If use a Year how dimension you can create the expression:

SUM(Sales) / SUM(TOTAL IF(vDate = AddYears(vDate), -1), Sales))

This line just sum the current year of your dimension

"SUM(Sales)"

This Line sum all lines (years), but you filtering just last year

"SUM(TOTAL IF(vDate = AddYears(vDate), -1), Sales))"

How to compare months

pover — Thu, 14 Oct 2010 05:45:24 GMT

One way to do this is create a chart with Month as your dimension, then create three expressions:

sum({$<Year={$(=max(Year))>} Sales)

sum({$<Year={$(=max(Year)-1)>} Sales)

column(1)/column(2)

The current year and past year depend on your selection of year.

Also, please note that AddYears is not an QlikView function.

Regards

How to compare months

Thu, 14 Oct 2010 13:36:14 GMT

Hi Karl,

I tried the example you have posted but with no good results because as months are dimensions for Jan 2009 only takes January Sales into account,

the big problem is that I need month as dimensions and also, users don't want to see sales period by period, they want to see only the growth by periods,

Any other idea?

Regards

How to compare months

Thu, 14 Oct 2010 13:46:58 GMT

Eduardo,

Thank you so much for your response, but this solution didn't work

How to compare months

Thu, 14 Oct 2010 13:51:26 GMT

Hi,

Do you have a example on qvw?

It's more easy to understand your problem if we see how you make your model.

Thanks

How to compare months

Thu, 14 Oct 2010 14:14:27 GMT

Hi Eduardo,

Here you have the example,

the expression "TOT" by now represents:

(Sales by month)/(Quarter Sales Average)

and we need to compare:

(Sales jan 2010) / (Sales jan 2009) and so on

In the expression your ideas are as comments if you want to review it,

thank you so much

How to compare months

Thu, 14 Oct 2010 15:04:15 GMT

Hi,

I use the function BEFORE to compare the dates and change your dimension to order correct '01/2009' '01/2010' and not by string.

See the example i create a table with the new expression.

How to compare months

Thu, 14 Oct 2010 16:05:29 GMT

Eduardo,

Could you help me using only the sales information in the previos month and not using "quarter sales information"

regards

How to compare months

Thu, 14 Oct 2010 17:26:39 GMT

Hi,

I read your script and the values of Quarter Sales Information are returned direct on your SQL Base.

You need to get this information from your BD.

How to compare months

Thu, 14 Oct 2010 17:34:53 GMT

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

How to compare months

pover — Thu, 14 Oct 2010 19:28:37 GMT

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.

How to compare months

Thu, 14 Oct 2010 19:36:45 GMT

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

How to compare months

Thu, 14 Oct 2010 19:44:56 GMT

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

How to compare months

Thu, 14 Oct 2010 20:22:58 GMT

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.

How to compare months

Thu, 14 Oct 2010 20:46:03 GMT

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