2 Replies Latest reply: Jul 18, 2011 10:57 AM by Sunil Kenth RSS

    Help with Set Analysis syntax

    Sunil Kenth

      Hi all,





      I am trying set the expression for  a Gauge chart to only calculate for the parent category of 'Telecoms' and for the current year and month.


      I have setup variables for the month and year as follows:


      vCurrentMonth = (Month(Max(Date))

      vCurrentYear = (Year(Max(Date))






      ='£'& Num (Sum({$< [Parent Category] = {'Telecoms'}, Month = {$(=$(vCurrentMonth))}, Year = {$(=$(vCurrentYear))}>}[Money Out]))


      The result i get for this expression is £0

      Any ideas?



        • Help with Set Analysis syntax
          Erich Shiino

          I guess you have a problem with the date format in set analysis.

          In this case, you should create a field on script where month is a number, instead of a formated date.

          The syntax on the script would be:

          Load (...)

          num(Month) as nMonth


          from / Resident... etc


          Then, the variable would be:

          vCurrentMonth = num((Month(Max(Date)) )


          Then, the expression would be:

          ='£'& Num (Sum({$< [Parent Category] = {'Telecoms'}, nMonth = {$(=$(vCurrentMonth))}, Year = {$(=$(vCurrentYear))}>}[Money Out]))


          Maybe the expression can be simpler, like this:

          nMonth = {$(vCurrentMonth)}


          nMonth = {$(=vCurrentMonth)}


          Hope this helps,