Last Year, Last Month and Last Quarter SAME DAY as of today

[pankaj999]

Hi,
I have following .qvd with Date as date_created and Numeric values as opp_value.

LOAD

date_created,

opp_value

FROM [lib://dbware/opps.QVD]

(qvd)

data looks something like this.

Now what I want to do with that data is to make a view as shown below :

I want to show numbers as above table and filter by Year Month.

Now important to note that it has to be dynamic, for e.g. if I select 2016-03 then my current month will change to March of 2016 and so will years and quarters accordingly. Also if I select the month(today()) then it should match to the same exact number of days as of current month. This as of the same day would go for quarters months and years numbers.

I want to know the easiest approach to this problem, I looked over the community but didn't get anything solid.

I hope the problem statement is clear, if not I would be happy to clarify.

[vitaliichupryna · Accepted solution]

Hi Pankaj,

First you should create master calendar

Creating A Master Calendar

and then you can use following link as example:

YTQ, QTD, MTD and WTD

Thanks,

Vitalii

[pankaj999]

Thanks VC

I already have a master calendar, now the other link is useful to some extent but like I said I need to move it by Year Month Filter and show last (M, Y, Q). Maybe need to modify the expressions but that's where I am stuck.