Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mahitham
Creator II
Creator II

Master Calender Urgent Help

Hi Experts,

Can any one please help me on below requirement.

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

I have to build a report like below.

YTD.png

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. 

Thanks in advance.

 

 

1 Solution

Accepted Solutions
sunny_talwar

Script

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

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

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

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

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

Temp:
LOAD Min(Date) as minDate,
	 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:
LOAD TempDate as Date,
	 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)

image.png

View solution in original post

5 Replies
sunny_talwar

Check this out

Canonical Date

mahitham
Creator II
Creator II
Author

Hi Sunny,
Thanks for your reply.
I am working on Qliksense. Could you please help me with my scenario.
sunny_talwar

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?

mahitham
Creator II
Creator II
Author

Hi @sunny_talwar ,

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.

 

Please help me on this.

Thanks in advance

 

Dates.png

sunny_talwar

Script

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

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

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

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

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

Temp:
LOAD Min(Date) as minDate,
	 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:
LOAD TempDate as Date,
	 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)

image.png