Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
sum({<Date_FY = , Date_Month = >} Sales)
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.
You can ignore the date selection using set analysis:
sum({<Date = >}Sales)
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?
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;
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
sum({<Date_FY = , Date_Month = >} Sales)
Spot on that!
Thanks for your help, really useful trick to know for the future also.
Stu