Forum Discussion

artfulmunkeey's avatar
artfulmunkeey
Helper I
5 years ago
Solved

Help with custom multi-filter(?)

Hi all,

 

I am having some trouble creating a custom filter and am starting to think I am over complicating the problem. Could some one please be kind enough to offer some advice?

 

I have the following categories for ActivityTypes (coming from timesheets):

  • Support
  • Non-Billable
  • External
  • CAPEX
  • BD
  • Sick
  • Other

I wish to categorise them as UtilisationTypes as follows:

  • Billable
  • Productive
  • Non-Productive

I can create a simple key with a header, however, the issue I have comes from the mapping and having duplicate values:

 

UtilisationTypeActivityType
ProductiveSupport Services (Corporate, BU, Division)
ProductiveNon-billable project (Direct costs for consultants)
ProductiveExternal Projects (FFP, LOE, FUP)
ProductiveCAPEX Projects
ProductiveBusiness Development
Non-ProductiveAbsence - Sickness
Non-ProductiveAbsence - Other
BillableExternal Projects (FFP, LOE, FUP)
BillableBusiness Development

 

Any advice would be welcome! Thanks, Alex

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    hello

     U can create a fact table for utilization type and map it to the other table. You can refer below. See below how I solved it. I hope this is what you wanted. 

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    hello

     U can create a fact table for utilization type and map it to the other table. You can refer below. See below how I solved it. I hope this is what you wanted. 

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    artfulmunkeey ,The information you have provided is not making the problem clear to me. Can you please explain with an example.

    Appreciate your Kudos.

    • artfulmunkeey's avatar
      artfulmunkeey
      Helper I
      5 years ago

      Sorry for not being clear.

       

      I attempted to solve the problem through relationships, however, sometimes an activity type belongs in more than one Utilisation Type, for example; BD and External Projects need to appear in Productive and Billable categories (eventually I wish to use Utilisation Type to filter my data).

       

      I hope the below image is of further use.

       

      Thanks in advance!

       

       

  • artfulmunkeey's avatar
    artfulmunkeey
    Helper I
    5 years ago

    Updated for clarity - 

     

    I attempted to solve the problem through relationships, however, sometimes an activity type belongs in more than one Utilisation Type, for example; BD and External Projects need to appear in Productive and Billable categories (eventually I wish to use Utilisation Type to filter my data).

     

    See below for my attempted logic

     

     

    • PaulDBrown's avatar
      PaulDBrown
      Community Champion
      5 years ago

      artfulmunkeey 
      How are you going to classify the rows in your fact table to cater for the duplicates? In other words, if your fact table has a row for "Business development", how will you know whether it's "Billable" or "Productive"? 
      It seems to me you will need to create anew column in the fact table to establish each row's "Utilisation Type". You can then establish separate dimension tables for "Activity Type" and "Utilisation type".