0 Replies Latest reply: Jun 2, 2014 11:10 AM by Eamonn Hannon RSS

    Filter the first value in a data set.

      Hi,

       

      I hope someone can help me with this issue I'm having trying to filter the first value in a dataset.  I haved a sample set of data as per the table below.  Im using some standard functions with set values but I'm trying to filter one and it's not working.

       

      My first expression is sum(BudgetValue) with Month as the dimension.  When a user selects a Cycle Month they get the Month Values for that cycle and this works correctly.

       

      My second expression gets the month values for the previous cycle with the following expression

      sum({<CycleID={$(=CycleID-1)} BudgetValue) and this also works.

       

      The expression thats not working is when I try to combine the 2 expressions above but only add the first value from the previous cycle.  For example, if a user selects Cycle Month Mar (Cycle ID 3), I want my table to display

       

      Feb  Mar  Apr  May  Jun

      240  230  270  240   220

       

      Using the following expression sum(BudgetValue) + sum({<CycleID={$(=CycleID-1)} I am getting the following

       

      Feb  Mar  Apr  May  Jun

      240  450  540  490   440

       

      i.e. it is adding all of the budget values for CycleID 3 and 2.  I only want to add the first value for CycleID 2 to all of the values for CycleID 3.  I have trying to use the First function and FirstSortedValue function but this hasn't worked.

       

      Can anyone advise on what function / set function I could use to get my desired results.

       

      Thanks.

       

       

      Header 1Header 2Header 3Header 4Header 5Header 6Header 7Header 8
      Cycle ID123456
      Cycle MonthJanFebMarAprMayJun
      Jan200250240280230210
      Feb240220270250220
      Mar230270240220
      Apr280250240
      May240230
      Jun220