topic Re: Converting Yearly Granularity Script to Include Monthly Granularity in QlikView

Converting Yearly Granularity Script to Include Monthly Granularity

alexandergrando — Mon, 01 May 2017 15:34:59 GMT

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;

Re: Converting Yearly Granularity Script to Include Monthly Granularity

Digvijay_Singh — Mon, 01 May 2017 17:07:43 GMT

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;

Re: Converting Yearly Granularity Script to Include Monthly Granularity

alexandergrando — Mon, 01 May 2017 19:01:16 GMT

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.