Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

giladmagori
New Contributor

Match of Year of Sales table and Year of Target table

Hi,

I have a fact table of sales,

It has an OrderDate field which connect to Master Calendar table.

I have also a TargetCategory table as below;

Target table.PNG

u can see below the relevnt part of the data model viewer,

viewer.PNG

i made this screen, and my question is how choosing of year will choose also the correct year of target(same solution for Month)?

SCREEN.PNG

Thanks,

Gilad

Tags (1)
1 Reply
OmarBenSalem
Esteemed Contributor

Re: Match of Year of Sales table and Year of Target table

It would be sthing like this:

here's a script you can use to build a test application and understand how to handle different dates:

SET DateFormat='YYYY-M-D';

Table1:

LOAD * INLINE [

ID, Seq, ActivityDate, Value1

1, 1, 2011-5-10,10

1, 2, 2011-5-12,2

2, 1, 2011-5-12,20

3, 1, 2011-6-15,40

];

Table2:

LOAD recno() as ID, * INLINE [

DepartureDate, ArrivalDate, Value2

2011-6-15,2011-6-16,5

2011-6-15,2011-6-17,10

2011-6-16,2011-6-16,20

];

OtherTable:

LOAD recno() as OtherID, * INLINE [

OtherDate, OtherValue

2011-5-12,2

2011-6-15,3

2011-6-17,5

];

Link:

LOAD

ID

,Seq

,ActivityDate as Date

,'Activity' as DateType

RESIDENT Table1

;

CONCATENATE (Link)

LOAD

ID

,DepartureDate as Date

,'Departure' as DateType

RESIDENT Table2

;

CONCATENATE (Link)

LOAD

ID

,ArrivalDate as Date

,'Arrival' as DateType

RESIDENT Table2

;

CONCATENATE (Link)

LOAD

OtherID

,OtherDate as Date

,'Other' as DateType

RESIDENT OtherTable

;

Calendar:

LOAD *

,date(monthstart(Date),'MMM YYYY') as Month

;

LOAD date(makedate(2011,5,1)+recno()-1) as Date

AUTOGENERATE 61

;

Capture.PNG

Capture.PNG

credits to johnw

Community Browser