Dated

[smilingjohn]

Hi All

I have a single table

In which there are 3 date fields which are

Entry Date

Collected Date

Sample Date

FInal Date

Among which i have used Entry date as Date  for Master Calendar ,

Now my question is if i want to use other date fields also in master calendar how do i use it

Iam using the below script for master calendar for the Entry Date . Which is working perfect and giving

me month, year quarter and so on . How do i use the above trhee date fields within the same master calendar ?

I have renamed Entry Date as Date

MaxMin:

LOAD Max(Date) as MaxDate,

  Min(Date) as MinDate

Resident ProductData;

SET vFiscalYearStartMonth = 4;

let vStartDate=Peek('MinDate');

let vEndDate=Peek('MaxDate');

FiscalCalendar:

LOAD

*,Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter

Dual(Text(Date(MonthEnd(Date), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name

LOAD

*,

Year(Date) AS Year, // Standard Calendar Year

Month(Date) AS Month, // Standard Calendar Month

Date(MonthEnd(Date), 'MMM') AS MonthName,  // Standard Calendar Month Name

Dual('Q' & Ceil(Month(Date)/3), Ceil(Month(Date)/3)) AS Quarter,  // Standard Calendar Quarter

Mod(Month(Date) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth,  // Fiscal  Month

Right(YearName(Date,0,$(vFiscalYearStartMonth)),4)as FiscalYear,

YearName(Date, 0, $(vFiscalYearStartMonth)) AS FiscalYearr;  // Fiscal Year

LOAD

Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date,

RangeSum(Peek('RowNum'), 1) AS RowNum

AutoGenerate vEndDate - vStartDate + 1;

Thanks in advance

[jagan]

Hi,

Can you attach some sample data and your expected result, so that it would be easier to understand and provide the solution.

Do you have multiple date fields in the same table? 

Check this link

Canonical Date

Regards,

jagan.

[jagan]

Also check this

Linking to two or more dates

Regards,

jagan.

[smilingjohn]

I have a single  table

In which there are 3 date fields which are

Entry Date

Collected Date

Sample Date

FInal Date

And Iam using Entry Date as Date in master calendar

[Digvijay_Singh]

There are different approaches mentioned in below link by experts.

Master Calendar Date from Multiple dates in dataset

[jagan]

Hi,

Check Canonical Date by HIC.  You have to implement this in your Dashboard.

Regards,

Jagan.

[smilingjohn]

Divijay

In my case all the 3 date fields are in a single table only

[jagan]

Thats Ok, go through that link and implement the same.  No matter if it is in the same table or different table.

DateBridge:

     Load OrderLineID, [Entry Date] as CanonicalDate, 'Entry' as DateType

          Resident OrderLines;

     Load OrderLineID, [Collected Date] as CanonicalDate, 'Collected' as DateType

          Resident OrderLines;

     Load OrderLineID, [Sample Date] as CanonicalDate, 'Sample' as DateType

          Resident OrderLines;

     Load OrderLineID, [Final Date] as CanonicalDate, 'Final' as DateType

          Resident OrderLines;

If you now want to make a chart comparing ordered and shipped amounts, all you need to do is to create it using a canonical calendar field as dimension, and two expressions that contain Set Analysis expressions:

     Sum( {$<DateType={'Entry'}>} Amount )

     Sum( {$<DateType={'Collected'}>} Amount )

[smilingjohn]

Hi Jagan

PLease find the attachemnt of the sample QVW file ..

Here Iam considering two tables within the same table .

[smilingjohn]

When i try to do this in my app

Its gving synthetic key .

Or can i  have only Date field in resident load ? like this below ......

Load  [Final Date] as CanonicalDate, 'Final' as Date

          Resident Prodcuts;