Qlik Community

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%

Thanks for your help

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

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


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

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

did not work

Thanks

MVP
MVP

Re: Help with Day and previous year

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;

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

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)

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.

Highlighted
MVP
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

Community Browser