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

    Solution to Difficult Question

    Ruan Greeff



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


      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.