Common Date and Calendar Help

[Anonymous]

Hi All,

I've been beating my head against my desk trying to get this to work.  I need to be able to compare a Sale Reps Sales vs their Quotas for months, quarters, and years  The Quotas are kept in an excel doc, their sales are kept via salesforce. I've tried many different things, and none seem to work.  I've read several well written blogs / posts by Rob Wunderlich and Henric Constrom on this very subject.

I've attached the most simplified version of a .QVW that I can get.

Can anyone help me out here?  What am I missing / doing wrong?  (To be fair - I have little clue to what is happening in the calendar subroutine. Building a calendar in that manner is new for me.  That was a copy from Rob Wunderlich's examples)

Thanks!

Dan Mercer

Added QVW with date(floor( Message was edited by: Dan Mercer

Message was edited by: Dan Mercer Added Document with Apply Map.  Still not functioning.

[jonathandienst]

Hard to see what's wrong, so I think you may need to be bit more specific. One possible problem is if one or more of the date sources are actually time stamps rather than pure dates.

The Date() function is a formatting function, it does not remove the time component and although the dates look the same, the underlying values are not. For this purpose, you  need

    =Date(Floor(Field1))  to ensure a pure date field

[Anonymous]

Jonathan,

Thanks for the feedback. What other specifics can I provide? For this application, at the end of the day there only needs to be one set of  common dates.  There is no order date, invoice date, shipping date type of problems here as the dates come from different tables.  It's really just sales periods (month, qtr, year) vs their quotas for the same periods. The users want to be able to select July 2015, and have the values (Sales and Quotas) corresponding to those dates returned.

The Quota information is loaded manually via excel.  Would the date format in the excel doc be an issue?

I wasn't ware of the date value you mentioned. I tried adding the Date(Floor(Field)) to each fact table and it didn't seem to work.  I did NOT add that to the calendar or datelink tables.  Since they are pulling from there, I shouldn't have to, correct?

[jonathandienst]

The floor function is necessary where there may be time components, such as a datetime field from your source system. It is not necessary for generated dates.

Ad you need to look at this document which explains ways to use multiple date fields:

Canonical Date

[Anonymous]

Jonathan,

Thanks for the floor tip - I didn't know that. I've viewed the document you mention before.  I didn't have much luck getting it to work either.  I don't know what it is I am doing incorrectly, and it's very frustrating.

[Anonymous]

I just added the date help 2 file.  Strangely, when I tried to do the applymap as you suggested, and used null()  all of the values in my common calendar disappeared.  I don't understand why that would happen.

[satishkurra]

Hi

Would like to understand 1 thing,

I understand you called QVC_Calendar, but where is the supporting scripts for that, Is it in the include statement?

Thanks

Satish

[Anonymous]

To be honest, that is not my code, so I am not 100% sure.  I believe that it is calling from the Calendar Sub.

[jonathandienst]

Dan Mercer wrote:

I just added the date help 2 file.  Strangely, when I tried to do the applymap as you suggested, and used null()  all of the values in my common calendar disappeared.  I don't understand why that would happen.

I don't think I said that? But be that as it may.

The mapping table uses a key, %key_OwnerID, which is an owner ID, but you are looking up dates in the mapping and they do not match, so the mapping return null. You probably need:

DateLink:

LOAD

   %key_OwnerID,

   ApplyMap('OwnerID2QuotaPeriodUser', %key_OwnerID, Null()) as CommonDate,

   'Opportunity' as DateType

RESIDENT OpportunityAccount

;

LOAD

   %key_OwnerID,

   ApplyMap('OwnerID2UserQuotaPeriod', %key_OwnerID, Null()) as CommonDate,

   'UserQuota' as DateType

RESIDENT UserQuotas

;

Note that Applymap works like an Excel vlookup() - it will return the first match and ignores duplicates

[Anonymous]

You're correct that you didn't say to use null - that was included in the post by Henric Cronström.

I copied your DateLink script.  It certainly helped.  But it still isn't working fully like I was hoping.  I'm not sure what my next step should be.