topic Re: Filter values- Bridge in App Development

Filter values- Bridge

dhanu_today — Tue, 23 Mar 2021 17:30:32 GMT

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

Re: Filter values- Bridge

jwjackso — Tue, 23 Mar 2021 21:07:52 GMT

I used Henric's Canonical Date for this problem.

Re: Filter values- Bridge

dhanu_today — Wed, 24 Mar 2021 02:27:12 GMT

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

Re: Filter values- Bridge

jwjackso — Wed, 24 Mar 2021 15:28:21 GMT

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

Re: Filter values- Bridge

dhanu_today — Wed, 24 Mar 2021 15:33:12 GMT

Hi,

Both fields are coming from same 1 table.

Thanks,

Dhanu

Re: Filter values- Bridge

jwjackso — Wed, 24 Mar 2021 15:45:33 GMT

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