2 Replies Latest reply: Apr 21, 2017 3:50 AM by Виталий Чуприна RSS

    Set expression select only necessary dates

    Виталий Чуприна

      Hi All,


      In my application I need to have following chart


       

      Year-MonthProduct %
      4,28%
      2016-040,17%
      2016-054,28%
      2016-064,19%
      2016-070,19%
      2016-084,28%
      2016-094,28%
      2016-104,28%
      2016-114,28%
      2016-124,28%
      2017-014,28%
      2017-024,28%
      2017-034,27%
      2017-044,27%


      I use expression below:


      =sum({$<DateType = {Product},ProductDateKey = P(DateKey),[Item Number]-={"Y*"}, [Date]={">$(=addmonths(today(),-12))"}>}[Quantity] * [Product Weigth])
      /
      sum({$<
      DateType = {Operation}, OperationDateKey = P(DateKey),
      [Date] = {">$(=addmonths(today(),-12))"} >}[Oper Quantity] * [Operation Weigth])

      Chart shows correct data only when I select one year-month value:


       

      Year-MonthProduct %
      0,22%
      2016-040,22%



       

      Year-MonthProduct %
      7,26%
      2016-087,26%

       

      Product and Operation tables have own date fields and they are linked by linkey table which contains - DataKey field

       

      The reason of problem is following: my tables are linked by Item Warehouse and this key is too wide, so in chart I show Product % based on data for all period. If there a way to show correct data by set analysis? Because when I try to use Item Warehouse Date key I lost part of data from Operation table