Link Table & Dates Help

[stuwannop]

Hi Everyone

Hope I can explain this properly. I have a sales order table, invoicing table and contracts table. All of them have a few dimensions in common which are Product, Region and Salesperson. I also wrote a master calendar for the dates.

I put all of these into a link table but the Contracts table is not date dependent, i.e. it doesn't have a date dimension. Now, predictably, if I select a date (e.g. 2013) I can see the sales orders and invoicing but I cannot see the sum of contracts on another tab (as it does not have a "date"). I want to be able to see a sum of the contracts table regardless of what date dimensions I use.

This would get me to a point where I could see YTD sales, YTD Invoicing and a total contract base.

Am I trying to do the impossible or is there a way around this?

Thanks
Stu

[#2]

I can think of one solution,  What you can do is create a ID field in contracts table and put that Id field in Link table. this way you are linking Contracts table and Link table. Now when you make selections the associated contracts records will be selected.

[Nicole-Smith]

You can ignore the date selection using set analysis:

sum({<Date = >}Sales)

[manojkvrajan]

Stuart, We can definitely look for workaround to link all these 3 tables using the date field. Can you share the qvw and sample data set?

[stuwannop]

Hi

Thanks for the suggestions, but it still doesn't seem to be working. I'm afraid I don't really have any sample data I can use as its quite report that took a long time to build. I've attached a screenshot of my schema does this help any? I've also pasted the script of the Lync table below.

LinkTable:

Load

CRMCustomers.accountid,

CRMCustomers.CUSRegion as Region,

CRMCustomers.accountno as Account,

CRMCustomers.AccountManagerProper as EmployeeName





Resident CRMCustomers;



//Take the OrderID and the dates from the next table, concatenate to table above.

Concatenate

Load

ORDID,

ORDSilo as Silo,

ORDGroup as Channel,

ORDCustomerNo as Account,

ORDDate as Date,

ORDRegionName as Region,

ORDSalesADAMProper as EmployeeName



Resident OrdersIn;



Concatenate

Load

Pipeline.opportunityid,

Pipeline.OPPSilo as Silo,

Pipeline.OPPCloseDate as Date,

Pipeline.OPPRegion as Region

//     OPPAccountManagerProper                              as EmployeeName



Resident Pipeline;



Concatenate

Load

CH.id,                                                  

CUS.accountnumber as Account,

CUS.Region as Region,

PR.Silo as Silo,

CUS.AccountManagerProper as EmployeeName



Resident CH;



Concatenate

Load

EMP.EmployeeID,

    EMP.Name as EmployeeName

Resident EMP;

 

[stuwannop]

Thanks Nicole this is helpful - I have several date fields (Date_FY, Date_Month etc.) What is the Syntax for excluding more than one date selection?

Thanks

[Nicole-Smith · Accepted solution]

sum({<Date_FY = , Date_Month = >} Sales)

[stuwannop]

Spot on that!

Thanks for your help, really useful trick to know for the future also.

Stu