5 Replies Latest reply on Jun 30, 2016 4:07 AM by Marie-claire Fournier

    Tricky Top 10 - involving table / LOD calculation & aggregation of result

    Marie-claire Fournier

      Hello,

       

      This is my first post, I would usually find solutions to my problems by browsing the forum, but this time I'm stuck.

      I'm attaching a simplified version of the twbx to help with the explanation.

       

      Data :

      - supplier / date / amount

      - flags (in the attachement I've only put 1 flag : with/without PO)

       

      Filters in Context (architecture can not be modified) :

      - date

      - flags

       

      User interaction :

      parameter : frequency

      The user is able to display the dashboard with calculation by month / by quarter / by year.

      Calculated field : Date Group which is a simple datetrunc based on the parameter

       

      Objectives :

      - Identify the top 10 suppliers for each Date Group (after context filters are applied)

      - Return pie with % of Spend with the top 10 suppliers against Spend in context.

       

      What I managed so far :

      - I've identified the top 10 suppliers for each date group using table calculation Rank_Unique( {fixed [supplier],[date group] , sum([amount])}) <11 calculated across supplier

       

      Struggling with :

      - Aggregation of the Spend for the top 10 Suppliers.

      I don't seem to be able calculate the sum of amount of the top 10 suppliers without having to place supplier and date group in the details.

      The pie is split at supplier and date group where I would only like to split it in or out of the top 10 suppliers.

       

      - I've tried LOD calculation but they don't work with table calculation

      something like : {Fixed [Flag top 10 suppliers] : sum([Amount])}

      but table calculation can't be used in LOD formula

       

      Has anyone experienced this problem ? Any suggestion ? Changing the approach and not use table calculation ?

       

      Thanks for your help.

      Marie-Claire