Filter values- Bridge

[dhanu_today]

Hi Everyone,

I have fields like Country,ID, Inputdate Resolvdate, sales,etc. In my report i have filters like Year,Month,week based on Resolvdate.

I have to create table/KPI's like when we select Month field or year field , then it has to display the Total (Sales) happen against Resolvdate,this is am getting. But how to Total(Sales) based on Inputdate.

Is there any work around on this?

Thanks,

Dhanu

[jwjackso]

I used Henric's Canonical Date for this problem.

[dhanu_today]

Thanks Jwjackso,

I have seen that article...But here I stucked at all fields are coming from same table not from 2 tables.

Any other approaches on this?

Thanks,

Dhanu

[jwjackso]

If the inputdate and resolvedate are on two different tables, create a master calendar.  Join inputdate and resolvedate to the master calendar.

[dhanu_today]

Hi,

Both fields are coming from same 1 table.

Thanks,

Dhanu

[jwjackso]

If both dates are coming from the same table, the Canonical Date solution will work.

//resolvedate mapping table

mapResolveDate:

mapping

Load RecordID // some unique id for the data record

          ResolveDate

Resident Data;

//inputdate mapping table

mapInputDate:

mapping

Load RecordID

          ImputDate

Resident Data;

//create your date bridge table

Dates:

Load RecordID

           ApplyMap('mapResolveDate',RecordID,Null()) as CanonicalDate,

          'Resolve' as DateType

Resident Data;

Concatenate

Load RecordID

           ApplyMap('mapInputDate',RecordID,Null()) as CanonicalDate,

           'Input' as DateType

ResidentData;

Build your master calendar off of the Dates table.

Data Table ->Dates->MasterCalendar