Remove circular reference

[andymanu]

Hi All,

I am working on an application which determines the customer served hours against the staff contracted hours.

I have attached a sample code to demonstrate my solution herewith and also have attached the excel files with the data for the application.

Unfortunately, I am getting the circular reference error and may be missing something within the code.

Appreciate your feedback to resolve the issue please.

The code looks like below;

Staff_Hours:

CrossTable(Date_Range, Hours_Worked,1)

LOAD

    *

FROM [lib://CGHS_ANDREW/Data/Test/Staff Sales per Staff Hour/Staff Hours Data.xlsx]

(ooxml, embedded labels, table is [Staff Hours]);

DateRange:

LOAD

    Date_Range,

    End_Date,

    Date#(trim(subfield([Date_Range],'-',1)),'DD/MM/YYYY') as MinDate,

               Date#(trim(subfield([Date_Range],'-',2)),'DD/MM/YYYY') as MaxDate

FROM [lib://CGHS_ANDREW/Data/Community Services/CS Performance per EFT/Mapping Data/Mapping Dates To Fortnight.xlsx]

(ooxml, embedded labels, table is [Fortnight Date Ranges]);

Customer_Contact_Hours:

LOAD

    "Customer ID",

    "Date",

    "Actual Hours",

    "Staff Name"   

FROM [lib://CGHS_ANDREW/Data/Test/Staff Sales per Staff Hour/Sales.xlsx]

(ooxml, embedded labels, table is Customer_Data);

LEFT JOIN (Customer_Contact_Hours)

Interval_Table:

               IntervalMatch(Date)

LOAD

               MinDate,

               MaxDate

Resident DateRange;

THanking you in advance.

Kind regards,

Andy

[andymanu]

Hi All,

I have re-arranged my code to remove the circular reference and now it is doing the expected job.

However, I got two date fields one being "Transaction Date" and the other being "Final_Date".

I am using a Master Calendar and the date I am using in the Master Calendar out of above two is the "Transaction Date".

I need to link all the date fields to the respective Date Range so that my analysis can be based on the "Date Ranges" of the year.

Please find the attached qvf file herewith.

My main requirement is, I want to analyse the Customer Served Hours against the actual Staff Hours.

Could someone help me to map the both Date fields to the same date range intervals please?

Appreciate your feedback.

Thank you.

Kind regards,

Andy