Converting Yearly Granularity Script to Include Monthly Granularity

[alexandergrando]

Hi. I have the following script at yearly granularity for the past 4 years. I would like to also include monthly granularity. Can someone please help me make the necessary additions to the script to do so? Thanks in advance.

Data:

LOAD

     %PropertyKey,

     BegDateOfOperation,

     EndDateOfOperation

FROM

[DimProperty1.qvd]

(qvd);

LEFT JOIN (Data)

LOAD year(Today())-IterNo()+1 as YearGranularity

AutoGenerate 1

While IterNo() <=4;

LEFT JOIN (Data)

LOAD

     Distinct YearGranularity,

     makedate(min(YearGranularity),1,1) as MinDateYear,

     makedate(max(YearGranularity),12,31) as MaxDateYear

Resident Data

Group by YearGranularity;

Final:

NoConcatenate

LOAD *,

          if(BegDateOfOperation <= MaxDateYear and EndDateOfOperation>=MinDateYear,1) as TotalPossiblePropertyFlag,

          if(BegDateOfOperation <= MaxDateYear and EndDateOfOperation>MaxDateYear,1) as ActivePropertyFlag,

          if(BegDateOfOperation <= MaxDateYear and BegDateOfOperation>=MinDateYear,1) as NewPropertyFlag,

          if(EndDateOfOperation <= MaxDateYear and EndDateOfOperation>=MinDateYear,1) as ClosedPropertyFlag

Resident Data;

DROP Table Data;

[Digvijay_Singh]

See if you can relate your need with this one -

Data:

LOAD * inline [

     %PropertyKey,BegDateOfOperation,EndDateOfOperation

     1, 01/01/2011,12/31/2013

     2, 05/01/2013, 09/30/2016

     3, 01/01/2014, 12/31/2016

     4, 01/01/2015, 03/31/2017 ];

LEFT JOIN (Data)

LOAD Month(Addmonths(Today(),1-IterNo())) as MonthGranularity,

  Year(Addmonths(Today(),1-IterNo())) as Year

AutoGenerate 1

While IterNo() <=48;

LEFT JOIN (Data)

LOAD

     Distinct MonthGranularity,

     makedate(min(Year),1,1) as MinDateYear,

     makedate(max(Year),12,31) as MaxDateYear

Resident Data

Group by MonthGranularity;

Final:

NoConcatenate

LOAD *,

          if(BegDateOfOperation <= MaxDateYear and EndDateOfOperation>=MinDateYear,1) as TotalPossiblePropertyFlag,

          if(BegDateOfOperation <= MaxDateYear and EndDateOfOperation>MaxDateYear,1) as ActivePropertyFlag,

          if(BegDateOfOperation <= MaxDateYear and BegDateOfOperation>=MinDateYear,1) as NewPropertyFlag,

          if(EndDateOfOperation <= MaxDateYear and EndDateOfOperation>=MinDateYear,1) as ClosedPropertyFlag

Resident Data;

DROP Table Data;

[alexandergrando]

Hi Digvijay,

Thank you for your response. Unfortunately, your code did not produce any months for the minimum and maximum dates. It also omitted some year for some reason. Below is a screenshot of your results:

I have attached a sample of my app along with data. My goal is to create a trend chart with a cyclical group dimension that cycles through time granularity. I was able to get the years to work, but I need help with quarters, months, weeks, and days. Any help would be greatly appreciated.