Master Calender Urgent Help

[mahitham]

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.

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.

[sunny_talwar]

Check this out

Canonical Date

[mahitham]

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]

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

[sunny_talwar · Accepted solution]

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)