Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Rename Date AS DateType
Load [Final Date] as CanonicalDate, 'Final' as DateType
Resident Prodcuts;
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
Regards,
jagan.
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
There are different approaches mentioned in below link by experts.
Divijay
In my case all the 3 date fields are in a single table only
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 )
Hi Jagan
PLease find the attachemnt of the sample QVW file ..
Here Iam considering two tables within the same table .
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;