topic Re: Ho to map different Date fields into one date field(Master Calendar) in App Development

Ho to map different Date fields into one date field(Master Calendar)

narayanaqlik — Thu, 14 Nov 2019 12:31:40 GMT

Hi All,

Have a nice day,

here i have 6 to 7 tables with two date fields like CreatedDate, UpdatedDate in each with different dates

all these 7 tables are related to different categories then i have calculated each category by using respective dates like

no.Of Compliance last 12 months in OSVersion from OSVersion tables, like that Google chrome , EnterPrise Domain

but here i need calculate in one last Dashboard Entire Application (Among the Categories) last 12 months Compliance Systems and Non Compliance Systems that should be from today() last 12 months data need to display.

so Here how can i map all the dates and how to calculate over all last 12 months data

Could you please help in this any one it will be very helpful to me

Thanks a lot every one.

Re: Ho to map different Date fields into one date field(Master Calendar)

Anil_Babu_Samineni — Fri, 15 Nov 2019 07:19:48 GMT

Try this way

Table1:
Load CreatedDate as DateField, Field1, Field2 from ..;
Concatenate
Load UpdatedDate as DateField, Field3, Field4 from ..;

MasterCalendar:
LOAD
Date(DateField) AS DateField,
Year(DateField) AS Year,
Month(DateField) As Month;

Load Date(MinDate + IterNo() -1 ) AS DateField While (MinDate + IterNo() - 1) <= Num(MaxDate);

Load Min(DateField) as MinDate,
Max(DateField) as MaxDate
Resident Table1;

Finally, To calculate last 12 months Data

Sum({<Month = {">=$(=AddYears(Max(Month),-1))<=$(=Max(Month))"}>} Measure) // Here, Month field will calculate from all fields and define that into master date field. From there, It will consider as Calendar Month to calculate arbitrary.