2 Replies Latest reply: Feb 12, 2018 9:26 AM by Bob Libby RSS

    Need some help with defining a set analysis formula

    Bob Libby

      Hi Experts,


      I'm having trouble correctly defining a set analysis expression in a script. When I use the formula directly in a chart it works as intended. When I try to define it in the script I haven't been able to make it work. Below is a simplified script demonstrating the problem with the qvw attached. When SelectedYear is selected the CYSales value should show the sales for that year.


      The Sales chart in the qvw shows the behavior of the formula applied in the script, the chart and via Settings->Valriable Overview

      The column labeled 'CYSales_Chart' demonstrates the desired results. Ideally I'd also like to use the CurrentYear variable as opposed to Max(SelectedYear) in the formula.


      Any help would be greatly appreciated.


      Thanks in advance,




      set CurrentYear = Max(SelectedYear);

      set LastYear = Max(SelectedYear)-1;



      set CYSales_Script = sum( {$<Year = {$(#=Max(SelectedYear))}>} Sales );




      load * Inline [

      Customer, Year, Sales

      CustA, 2016, 10000,

      CustA, 2017, 12000,

      CustA, 2018, 15000,

      CustB, 2016, 1000,

      CustB, 2017, 1200,

      CustB, 2018, 1500,

      CustC, 2016, 100,

      CustC, 2017, 120,

      CustC, 2018, 150,





      load Distinct Year as SelectedYear Resident Sales;