Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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)
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
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;
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
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)
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.
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