# QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

## Help with Day and previous year

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

Tags (2)
9 Replies

## Re: Help with Day and previous year

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.

talk is cheap, supply exceeds demand
Not applicable

## Re: Help with Day and previous year

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%

## Re: Help with Day and previous year

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':

talk is cheap, supply exceeds demand
Not applicable

## Re: Help with Day and previous year

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

did not work

Thanks

MVP

## Re: Help with Day and previous year

Maybe along these lines:

INPUT:

Sales

FROM

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

[Same day previous year]

FROM

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

Day as [Same day previous year],

Sales as [Sales previous year]

Resident INPUT;

Not applicable

## Re: Help with Day and previous year

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

MVP

## Re: Help with Day and previous year

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:

Day as TableDay,

Day as [Day Current Year],

[Same day previous year]

FROM

(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)

Not applicable

## Re: Help with Day and previous year

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.

MVP

## Re: Help with Day and previous year

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