2 Replies Latest reply on Aug 10, 2016 2:40 PM by ahmed.hasnaoui

    Help me with relationships and filter by month

    ahmed.hasnaoui

      Ok I am really stuck here. I have three data sources: A, B & C.

      A & B are linked with external employee ID.

      A has almost the same key measures like B but we need to figure out variances.

      C is for the look up of the working hours and days per month in 2016 to calculate the FTE for A.

      A has a month dimension but B doesn't.

       

      Problem: B has the FTE and hours values per month in separate columns and no extra month dimension. e.g.:

       

      ID
      FTE AprilHours AprilFTE MayHours May
      111231123
      213451456
      3

      0,6

      7890,6789

       

      I pivot the columns and get this:

       

      IDPivot Field NamePivot Field Value
      1FTE April

      1

      1Hours April123
      2FTE April1
      2Hours April

      345

      3Hours April0,6
      3Hours April789
      1FTE May1
      1Hours May123
      2FTE May1
      2Hours May456
      3FTE May0,6
      3Hours May789

       

      From Pivot Field Name I created the Month Dimension using these calc. fields:

      First:

      "Pivot Field Name as INT"

       

      CASE [Pivot Field Name]
      WHEN "FTE April"
      THEN 4
      WHEN "FTE May"
      THEN 5
      END
      

       

      Then:

      "Month"

       

      DATE("01" + "/" + STR([Pivot Field Name as INT]) + "/" + "2016")
      

       

      result:

       

      IDPivot Field NamePivot Field ValueMonth
      1FTE April

      1

      01.04.2016
      1Hours April123Null
      2FTE April101.04.2016
      2Hours April

      345

      Null
      3FTE April0,601.04.2016
      3Hours April789Null
      1FTE May101.05.2016
      1Hours May123Null
      2FTE May101.05.2016
      2Hours May456Null
      3FTE May0,601.05.2016
      3Hours May789Null

       

      Now all my sources have the month dimension (even though Hours have null, I am ok with that for now).

      I thought that I now can use the month dimensions to create a relationship between all three sources so that when I filter on month from C the filter will applied to all sources...but I get only * values

      When I drag only the months in my sheet I have good values, but as I drag the ID I get * values...

       

      Any ideas?