by tomas.trnka@keboola.com -- Sometimes you question yourself with business questions like: How are we doing so far this year? How many products did we sold compared to the plan? Or - How are we doing compared to the previous year? You can find many resources why it might be a good idea to look at your data from YTD (Year-to-date) or YoY (Year-over-Year) perspective. (good start: https://en.wikipedia.org/wiki/Year-to-date or https://community.qlik.com/blogs/qlikviewdesignblog/2014/03/03/ytd) Prerequisites 1x fact column with numbers (pieces, money) you'd like to aggregate (SUM for example) 1x date column relating to the fact column (usually called transaction or event date) + You will need to implement DDD (Disconnected Date Dimension) to your project http://developer.gooddata.com/article/painless-disconnected-date-dimension First code tells us how many products we sold this year. The metric is so smart that it takes the maximum date from the filter on dashboard, recognizes the maximum date's year and sets the filter range to 1.1. of selected year to the maximum date selected. So for example if you select 2015-07-24, the metric will return SUM of sold goods between 2015-01-01 and 2015-07-24 SELECT SUM(kusy (prodej)) WHERE Year (date_prodej)= (SELECT MAX(SELECT Year (ddd) BY Date (ddd)) BY ALL OTHER EXCEPT Date (ddd)) AND Day of Year (date_prodej)<= (SELECT MAX(SELECT Day of Year (ddd) BY Date (ddd)) BY ALL OTHER EXCEPT Date (ddd))If you want to use the same filter for showing the same date range, but 1 year back (i.e. from 2014-01-01 2014-07-24) you can use the following almost identical metric: SELECT SUM(kusy (prodej)) WHERE Year (date_prodej)= (SELECT MAX(SELECT Year (ddd) BY Date (ddd)) BY ALL OTHER EXCEPT Date (ddd)) - 1 AND Day of Year (date_prodej)<= (SELECT MAX(SELECT Day of Year (ddd) BY Date (ddd)) BY ALL OTHER EXCEPT Date (ddd))You are now ready to do the Year to date and Year over Year comparisons using 1 simple date filter on dashboard. Both metrics are captured on the screenshot bellow:  Now lets select the same day 1 year back to check whether those metrics work correct (note - we have data only for 2 years, thats why "previous year" metric is blank)  Brilliant - previous and selected years' numbers fit when we change the year in the filter. Note when trying to make Week over Week comparisons the metric will be slightly different since you also want take in account the current day of week. SELECT SUM(kusy (Prodej)) WHERE Week (Mon-Sun)/Year (date_prodej)= (SELECT MAX(SELECT Week (Mon-Sun)/Year (DDD) BY Date (DDD)) BY ALL OTHER EXCEPT Date (DDD)) - 1 AND Day of Week (Mon-Sun) (EventCreated)<= THIS The metric above will SELECT all the sold items sold in the days of week prior to "THIS" today (tuesday) with tuesday last week. Enjoy |