topic Table with data comparation in QlikView

Table with data comparation

Wed, 14 Apr 2010 13:50:28 GMT

Dear all I have to create a table with comparation between months and I have no Idea how to do that.

I have a table with a structure like

Date Sales

01/01/2009 20

02/01/2009 40

02/02/2009 50

.....

I have a system variable with a date and a want to do.

From the year of that variable obtain the next table.

Year

Mont of the data Sales

Example

01/01/2009 10

01/02/2009 2

01/02/2009 3

01/03/2009 5

2009

January

February -50%

March 0 %

....

And in every month diference with the previous in %.

Can someone help me with the code of the expressions / dimensions.

Best regards

Table with data comparation

sparur — Wed, 14 Apr 2010 14:13:17 GMT

If i Understand your question, that this expression can help you:

sum(Sales) / above(sum(Sales))

dimension by Month

Table with data comparation

justinasp — Wed, 14 Apr 2010 14:19:11 GMT

Try this:

test1:
LOAD * INLINE
[
yearmonth,sales
201001,10
201002,3
201002,2
201003,5
];
test2:
LOAD yearmonth,
 sum(sales) as msales
RESIDENT test1
GROUP BY yearmonth
ORDER BY yearmonth;
test3:
LOAD yearmonth,
 msales,
 (msales-numsum(peek(msales)))/numsum(peek(msales)) as percentage
RESIDENT test2;
DROP TABLE test2;

Cheers,

Justinas

Table with data comparation

Wed, 14 Apr 2010 14:35:49 GMT

OK Thanks,

that's the answer.

But only one more thing...

How to compare the same month on diferent years.

Best regards

Table with data comparation

Wed, 14 Apr 2010 14:49:15 GMT

Hi sparur. That's the solution, but.... How can I do to compare same month on diferent years.

Best regards.

Table with data comparation

Miguel_Angel_Baeyens — Wed, 14 Apr 2010 15:50:42 GMT

Hello Jordi,

Given that Year is a dimension showed in a column in your table, and "Sales" the expression you use to calculate monthly sums, and years ordered higher to lower, you can do

([Sales] - after([Sales])) / after([Sales])

After() function will take next column's value.

Hope that helps.