Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a cross table of data like below:
CrossTable(FiscalMonth, Data, 3)
LOAD Key,
Category,
Year,
Jan,
Feb,
Mar,
Apr,
May,
Jun,
Jul,
Aug,
Sep,
Oct,
Nov,
Dec
That I need to link to both the data (Category AS AEA_EUROPE) and also need to link the month and year to the calendar (either FiscalMonth and FiscalYear or FiscalMonthYear) but when I connect the both, I get loops. What is the best way to get them all linked without getting these loops?
Thanks
Depending on how your FiscalMonthYear is formatted,
Data:
CrossTable(FiscalMonth, Data, 3)
LOAD Key,
Category,
Year,
Jan,
Feb,
Mar,
Apr,
May,
Jun,
Jul,
Aug,
Sep,
Oct,
Nov,
Dec;
Data2:
load Key,
Category,
FiscalMonth&'-'&Year as FiscalMonthYear,
Data
Resident Data;
Drop Table Data;
How do I connect the category field though?
This doesn't match them
Is AEA_EUROPE a calendar field? Can you show the fields of the calendar?
No AEA_EUROPE is just the name of the category field in another table. It has the same values as the Category field in the cross table.
The calendar has the following fields:
ENTERED_PERIOD_NAME
PERIOD_NAME
PERIOD_YEAR
PERIOD_NUM
QUARTER_NUM
PERIOD_DATE
QUARTER_END_DATE
QUARTER_START_DATE
YEAR_END_DATE
YEAR_START_DATE
DATE
Year
Month
MonthYear
MonthNumYear
Quarter
QuarterYear
Week
WeekNumber
Weekday
Day
DateMMDD
FiscalDay
MONTH_START_DATE
MONTH_START
MONTH_END_DATE
MONTH_END
QUARTER_START
QUARTER_END
YEAR_START
YEAR_END
FiscalMonth
FiscalMonthYear
FiscalQuarter
FiscalQuarterYear
and then a bunch of flags.
I have the format of FiscalMonth as MMM and Year as YY so it matches with FiscalMonthYear which is MMM-YY but I'm not having much luck of putting the data together
As long as the other table isn't connected to the calendar, can't you rename Category as below?
Data2:
load Key,
Category as AEA_EUROPE,
FiscalMonth&'-'&Year as FiscalMonthYear,
Data
Resident Data;
Problem is that there is another orders table that is linked to the calendar as SHIPMENT_DATE AS DATE
Are you getting a circular reference between the new data, the orders table and the calendar. If so, which fields are common?
Here is a screenshot of the tables with the link I also need..
Depending on how you are going to use the AEA_DATA, you could link the AEA_DATA to the ITEM table and then use the P() function in your calculations for the AEA_DATA.
AEA_DATA would look like this:
FiscalMonthYear as AEA_FiscalMonthYear,
Key,
Category as AEA_EUROPE,
AEAData
In calculations for the AEA_DATA,
Sum({$<AEA_FiscalMonthYear=P(FiscalMonthYear)>}AEAData)