8 Replies Latest reply: Aug 24, 2017 3:02 PM by Sunny Talwar RSS

    Trend in Pivot table with Value list not working

    Mohammad Adnan Ansari

      I have define the below value list:

       

      valuelist('Sales', 'Cost of Sales', 'GP')

       

      Now I have define a Value formula as below:

       

      if(  Valuelist($(vListHeads1)) ='Sales', $(vSalesCY),

           if(  Valuelist($(vListHeads1)) ='Cost of Sales', $(vCostOfSalesCY),

                if(  Valuelist($(vListHeads1)) ='GP', $(vGPCY),0

                )

           )

      )

       

       

      This function works fine.

       

      Now I need to use a pivot table to show the trend by month of these variables. The filter is based on the month. If I have no selection, it shows totals for all months of the current year. but when I select a month, it don't show the totals (by month) for the that year. it just shows that month only.

       

      The below is the formula I have used for vSalesCY.

       

      If(getselectedcount([Month Year])<1,

      Sum({$<[Statistics Name]={'Sales - Retail'}, [Year] = {"$(=Year(AddMonths(Today(),-1)))"} ,[Month No]=,[Month Year]=,[Month]=,[MonthID]= >} [Gl Report Month Balance] * Multiplier)

      ,

      Sum({$<[Statistics Name]={'Sales - Retail'}, [Year] = {"$(=Max(Year))"},[Month No]=,[Month Year]=,[Month]=,[MonthID]= >} [Gl Report Month Balance] * Multiplier)

      )