# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Contributor

## Master Calender Urgent Help

Hi Experts,

In my application I have 3 different dates like Date1, Date2 and Date3.

I have to build a report like below.

Here In the Month Year column need to take  from today to last 12 months data. For this please help me to create a Master calendar based on below logic and please help me how to use this same calendar month year for all the measures coming from different dates.

It has a 12-month (rolling) reporting period. The end date of the reporting period shall be the last calendar day of the month that immediately precedes the run date. The start date of the reporting period shall be the date that fell exactly 12 months prior to the end date of the reporting period.

Then for New Visitors measure considering Date1 field. Measure definition is Count of Ids where Date1 is within the reporting period

Then for Unique Visitors measure considering  Estimate Date field. Measure definition is Count of Ids where Date2 is within the reporting period

In two measures using two different dates but the requirement is dimension has to be global Month year field which has to be applicable for both measures dates. Please help me to on this.

1 Solution

Accepted Solutions
MVP

## Re: Master Calender Urgent Help

Script

```Test:
Date(Floor(Date1)) as Date1,
Date(Floor(Date2)) as Date2,
Date(Floor(Date3)) as Date3,
"count"
FROM [lib://Test/Test2.qvd] (qvd);

BridgeTable:
Date1 as Date,
'Date1' as Flag
Resident Test;

Concatenate (BridgeTable)
Date2 as Date,
'Date2' as Flag
Resident Test;

Concatenate (BridgeTable)
Date3 as Date,
'Date3' as Flag
Resident Test;

QuartersMap:
Mapping
'Q' & Ceil (rowno()/3) as Quarter
AutoGenerate(12);

Temp:
Max(Date) as maxDate
Resident BridgeTable;

LET varMinDate = Num(Peek('minDate', 0, 'Temp'));
LET varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:
LOAD \$(varMinDate) + Iterno()-1 as Num,
Date(\$(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1
While \$(varMinDate) + IterNo() -1 <= \$(varMaxDate);

MasterCalendar:
Week(TempDate) as Week,
Year(TempDate) as Year,
Month(TempDate) as Month,
Day(TempDate) as Day,
YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
inyear(TempDate, Monthstart(\$(varMaxDate)),-1) as RC12,
Date(MonthStart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(WeekStart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;

DROP Table TempCalendar;```

Table

Dimension

`MonthYear`

Expressions

```Sum({<Flag = {'Date1'}>}count)
Sum({<Flag = {'Date2'}>}count)
Sum({<Flag = {'Date3'}>}count)```

5 Replies
MVP

Check this out

Canonical Date

Contributor

Hi Sunny,
MVP

## Re: Master Calender Urgent Help

Sure what do you need help with? All I see is an image and a description which is very generic and for a generic question, I can only provide a generic answer. I did provide you the link which might help you, but if you need more help please provide a sample or a sample data and explain what it is doing today and what do you want it to do?

Contributor

## Re: Master Calender Urgent Help

Thanks for your help. Please find the below attached app and sample data.

Here Date1, Date2 and Date3 are the fields coming from one table and this table was linked with another table based on ID that table don't have the date field. In this table have 3 date fields, from these 3 date fields need to create one common Month year field.

In a pivot table need to show

Dimension as : MonthYear(which contains from today to last 12 months )

Measure1: sum of count for Date1 of relevant months.

Measure2: sum of count for Date2  of relevant months.

Measure3; sum of count for Date3  of relevant months.

MVP

## Re: Master Calender Urgent Help

Script

```Test:
Date(Floor(Date1)) as Date1,
Date(Floor(Date2)) as Date2,
Date(Floor(Date3)) as Date3,
"count"
FROM [lib://Test/Test2.qvd] (qvd);

BridgeTable:
Date1 as Date,
'Date1' as Flag
Resident Test;

Concatenate (BridgeTable)
Date2 as Date,
'Date2' as Flag
Resident Test;

Concatenate (BridgeTable)
Date3 as Date,
'Date3' as Flag
Resident Test;

QuartersMap:
Mapping
'Q' & Ceil (rowno()/3) as Quarter
AutoGenerate(12);

Temp:
Max(Date) as maxDate
Resident BridgeTable;

LET varMinDate = Num(Peek('minDate', 0, 'Temp'));
LET varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:
LOAD \$(varMinDate) + Iterno()-1 as Num,
Date(\$(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1
While \$(varMinDate) + IterNo() -1 <= \$(varMaxDate);

MasterCalendar:
Week(TempDate) as Week,
Year(TempDate) as Year,
Month(TempDate) as Month,
Day(TempDate) as Day,
YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
inyear(TempDate, Monthstart(\$(varMaxDate)),-1) as RC12,
Date(MonthStart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(WeekStart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;

DROP Table TempCalendar;```

Table

Dimension

`MonthYear`

Expressions

```Sum({<Flag = {'Date1'}>}count)
Sum({<Flag = {'Date2'}>}count)
Sum({<Flag = {'Date3'}>}count)```