topic Re: Help with Day and previous year in QlikView

Help with Day and previous year

Thu, 11 Oct 2012 15:44:26 GMT

Hello,

Sales table contains sales, day, store, etc

Another table has the day and the equivalent day of previous year. Fi: 2012 October 10 is equivalent to 2011 October 14; 2012 September 28 is equivalent to 2011 September 30 and so on.

When I want to know sales from one period of time, some days one month, etc vs the previous year I solved it with set analysis and concat; it works with city, region, store, brand, etc. But with Day as dimension it doesn’t work

How can I get this table?:

Day	Sales	Sales previous year	%
28/09/2012	100,00 €	110,00 €	90,91%
29/09/2012	120,00 €	98,00 €	122,45%
30/09/2012	140,00 €	136,00 €	102,94%

In this table, first row, Sales previous year should be the sales of 2011 September 30 because is the equivalent day of 2012 September 28

Any help

Thanks

Re: Help with Day and previous year

Gysbert_Wassenaar — Thu, 11 Oct 2012 16:47:47 GMT

This can be done with set analysis. You can see on the tab Set Analysis - Static in the attached ytd-set-analysis.qvw how this is done.

Re: Help with Day and previous year

Mon, 15 Oct 2012 07:37:39 GMT

Thanks but it is not that I am looking for. I am going to add some explanation in order to try to explain it a little bit more

Sales table:

Day	Sales
30/09/2011	110,00 €
27/09/2011	98,00 €
22/09/2011	136,00 €
……	……
28/09/2012	100,00 €
29/09/2012	120,00 €
30/09/2012	140,00 €

Equivalents Days:

Day	Same day previous year
28/09/2011	29/09/2010
29/09/2011	23/09/2010
30/09/2011	28/09/2010
…….	……
28/09/2012	30/09/2011
29/09/2012	27/09/2011
30/09/2012	22/09/2011

And the graphic (table) that I want to show is the showed in my previous post

Day	Sales	Sales previous year	%
28/09/2012	100,00 €	110,00 €	90,91%
29/09/2012	120,00 €	98,00 €	122,45%
30/09/2012	140,00 €	136,00 €	102,94%

Thanks for your help

Re: Help with Day and previous year

Gysbert_Wassenaar — Mon, 15 Oct 2012 08:17:46 GMT

Well, it is not what you're looking for in the sense that you can just copy/paste the expression. Instead you need to look at what the expressions do and adapt that to your situation.

This might work to calculate 'Sales previous year':

=sum( {$<Day={'=makedate(addmonths(Day),-12)-1'}>} Sales)

Re: Help with Day and previous year

Mon, 15 Oct 2012 08:52:02 GMT

Thanks Gysbert,

Maybe I did not explain it well because my english is not good enough.

Every day has an equivalent day in the previous year, for instance:

2012sep28 has to be with 2011sep30

2012sep29 has to be with 2011sep27

2012sep30 has to be with 2011sep22

and so on

Then: =sum( {$<Day={'=makedate(addmonths(Day),-12)-1'}>} Sales)

did not work

Thanks

Re: Help with Day and previous year

swuehl — Mon, 15 Oct 2012 09:03:39 GMT

Maybe along these lines:

INPUT:

LOAD Day,

Sales

FROM

[http://community.qlik.com/thread/63634?tstart=0]

(html, codepage is 1252, embedded labels, table is @2);

left join LOAD Day,

[Same day previous year]

FROM

[http://community.qlik.com/thread/63634?tstart=0]

(html, codepage is 1252, embedded labels, table is @3);

left join (INPUT) LOAD

Day as [Same day previous year],

Sales as [Sales previous year]

Resident INPUT;

Re: Help with Day and previous year

Mon, 15 Oct 2012 09:41:47 GMT

Thanks swuehl,

The problem with that is that sales table is a big table (2,5GB qvd file) with info about a lot of dimensions and other metrics and is not possible to that.

The expression I am using in other dimension (Article, center, brand, etc.) is:

Sum({<DAY={$(=concat( DAY_PREV_YEAR ,','))}>} Sales) and it works right

But with DAY as dimensión is not usable

Thanks

Re: Help with Day and previous year

swuehl — Mon, 15 Oct 2012 10:06:31 GMT

A set expression is evaluated once per chart, not per dimension value. But you would need to look up the corresponding DAY_PREV_YEAR per DAY value. So I don't think set analysis is the way to go here.

I still suggest considering a script based solution. My first post should just give you an idea on how you can maybe do this. This idea results in having a new field [Sales previous year] in your sales table.

Using joins is probably not the best approach when you are coping with large data sets. But you can achieve the same with lookup /mapping tables. Please refer to

http://community.qlik.com/blogs/qlikviewdesignblog/2012/09/18/one-favorite-function-applymap

Another, also script based, idea is transforming your equivalent days table like this:

Crosstable (Period, Day) LOAD

Day as TableDay,

Day as [Day Current Year],

[Same day previous year]

FROM

[http://community.qlik.com/thread/63634?tstart=0]

(html, codepage is 1252, embedded labels, table is @3);

Then use TableDay as dimension in your chart and as expresssion:

=sum({<Period = {'Day Current Year'}>}Sales)

=sum({<Period = {'Same day previous year'}>} Sales)

Re: Help with Day and previous year

Fri, 19 Oct 2012 14:32:46 GMT

Thanks swuehl,

The first idea kill the server not in load but at query time.

When I have time I will test the crosstable approach.

Re: Help with Day and previous year

swuehl — Fri, 19 Oct 2012 17:33:24 GMT

Not sure why the first approach (I assume the mapping load) should affect your runtime performance. The idea is that you get the same data model than with my first reply (using left joins). But I now think the CROSSTABLE solution is probably better, anyway.

Have a nice weekend,

Stefan