Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Joining Cross Table with data and calendar

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

11 Replies
Highlighted
Specialist
Specialist

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;

Creator
Creator

How do I connect the category field though?

This doesn't match them

Highlighted
Specialist
Specialist

Is AEA_EUROPE a calendar field?  Can you show the fields of the calendar?

Highlighted
Creator
Creator

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

Highlighted
Specialist
Specialist

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;

Highlighted
Creator
Creator

Problem is that there is another orders table that is linked to the calendar as SHIPMENT_DATE AS DATE

Highlighted
Specialist
Specialist

Are you getting a circular reference between the new data, the orders table and the calendar.  If so, which fields are common?

Highlighted
Creator
Creator

Here is a screenshot of the tables with the link I also need..

Market.PNG

Highlighted
Specialist
Specialist

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)