5 Replies Latest reply: Sep 7, 2017 7:44 AM by Sunny Talwar RSS

    Solution to Difficult Question

    Ruan Greeff

      Hi,

       

      I will try and keep this as simple as possible.  I have the below datamodel.

      Capture.PNG

      I have two seperate sources of data.  And the only link i could establish between them was a FinancialYearMonth key that i created.

       

      The issue is , that the relationship is not good enough , and creates problems within my data when calculating formulas.

      The conflict between two dimensions that are not related is the cause of the issue.

       

      The calculation i use is the below: however i need to present it in a straight table using the Product group as a dimension.

       

      My set analysis is as follows : Sum({$<Category = {'Flour'}>}Amount)

       

      This means that without a decent relationship between the values, the results are invalid.

       

      Can anyone recommend a solution to the problem, the issue here is, i wouldve liked to create a unique key ,consisting of FinYear&Month&Productgroup as Key but the dates are all in the calendar table, and product group is in a seperate table altogether.

       

      If i had done this, the link would be easy, as in the other table, the dates and classification are in the same table, meaning that FinYearMonth&Classification would be easy to accomplish.  Bare in mind that classification and product group are the same.  i just need the relationship to be on the level of product group.

       

      Hope the above provides enough detail.  I was thinking a link table but as I have never used one, im not sure whether that solution would be applicable with the above.

       

      Any advice or help would be appreciated.

       

      Regards,