Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Converting Yearly Granularity Script to Include Monthly Granularity

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;

2 Replies
Highlighted
Master III
Master III

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;

Contributor III
Contributor III

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:

Dig Capture.PNG

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.